w e l c o m e    t o                                                                                                                            Excel -Vba
            w w w. f e l i c i n i. com                                                                                          Home    Links    Technet    About Us

Autofilter

posted Feb 20, 2020, 6:10 AM by Roberto Felicini

Turn off Autofilter in VBA
 
Public Sub KillFilter()
If ActiveSheet.AutoFilterMode Then
     ActiveSheet.AutoFilterMode = False
End If
End Sub

Turn on Autofilter in VBA

Public Sub StartFilter()
If Not ActiveSheet.AutoFilterMode Then
     ActiveSheet.Range("A1").AutoFilter
End If
End Sub



VBA Do Until Loop

posted Feb 18, 2020, 11:53 PM by Roberto Felicini


Sub Do_Until_Ex2()
Dim Y As Long
Y = 1
Do
  Sheets("Example 2").Cells(Y, 1).Value = Y * Y
  Y = Y + 1
Loop Until Y = 11

End Sub

Ways To Find The Last Column

posted Feb 18, 2020, 11:48 PM by Roberto Felicini

Sub FindingLastColumn()

'PURPOSE: Different ways to find the last column number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastColumn As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

'Ctrl + Shift + End
  LastColumn = sht.Cells(7, sht.Columns.Count).End(xlToLeft).Column

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column

'Using Table Range
  LastColumn = sht.ListObjects("Table1").Range.Columns.Count

'Using Named Range
  LastColumn = sht.Range("MyNamedRange").Columns.Count

'Ctrl + Shift + Right (Range should be first cell in data set)
  LastColumn = sht.Range("A1").CurrentRegion.Columns.Count

End Sub

How To Expand Your Range To The Last Row

posted Feb 18, 2020, 11:48 PM by Roberto Felicini

Dim DataRange As Range

Set DataRange = Range("A1:M" & LastRow)

convert selected cells in number

posted Feb 18, 2020, 11:43 PM by Roberto Felicini

Sub Enter_Values()

 Range("B2", Range("B" & Rows.Count).End(xlUp)).Select
   For Each xCell In Selection
      xCell.Value = xCell.Value
   Next xCell
  
   Selection.NumberFormat = "0"
   Range("a2").Activate
  
End Sub

Ways To Find The Last Row

posted Feb 18, 2020, 11:38 PM by Roberto Felicini

Sub FindingLastRow()
'PURPOSE: Different ways to find the last row number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

'Using Find Function (Provided by Bob Ulmas)
  LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

'Using SpecialCells Function
  LastRow = sht.Cells.SpecialCells(xlCellTypeLastCell).Row

'Ctrl + Shift + End
  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row

'Using Table Range
  LastRow = sht.ListObjects("Table1").Range.Rows.Count

'Using Named Range
  LastRow = sht.Range("MyNamedRange").Rows.Count

'Ctrl + Shift + Down (Range should be first cell in data set)
  LastRow = sht.Range("A1").CurrentRegion.Rows.Count

End Sub

convert cell TXT to cell VALUE

posted Nov 11, 2019, 10:10 AM by Roberto Felicini

Sub Enter_Values()
   For Each xCell In Selection
      xCell.Value = xCell.Value
   Next xCell
End Sub

VBA remove index cell used

posted Sep 23, 2016, 12:35 PM by Roberto Felicini   [ updated Sep 23, 2016, 12:36 PM ]

Dim sh As Worksheet, x As Long
   For Each sh In ActiveWorkbook.Worksheets
     x = sh.UsedRange.Rows.Count
   Next sh

VBA - date and time on save doc

posted Sep 9, 2015, 2:32 AM by Roberto Felicini

Alla chiusura del workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Me.Worksheets("Foglio1")
.Range("A1").Value = "=TODAY()"
End With
End Sub


Ad ogni salvataggio(ribadisco: ogni salvataggio):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
With Me.Worksheets("Foglio1")
.Range("A1").Value = "=TODAY()"
End With
End Sub

Qui metto la data in A1 del Foglio1. Modificare di conseguenza.

Visualizzare data e nome utente ultima modifica

posted Sep 9, 2015, 2:28 AM by Roberto Felicini

Comunque, partendo da qui:

http://www.erlandsendata.no/english/index.php?d=envbaosusername

Con questo codice da mettere in un modulo:

Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function ReturnUserName() As String

    Dim rString As String * 255
    Dim sLen As Long
    Dim tString As String

    tString = ""

On Error Resume Next

    sLen = GetUserName(rString, 255)
    sLen = InStr(1, rString, Chr(0))

    If sLen > 0 Then
        tString = Left(rString, sLen - 1)
    Else
        tString = rString
    End If

On Error GoTo 0

    ReturnUserName = UCase(Trim(tString))

End Function


E questo da mettere nell'evento Open di
ThisWorkbook:

Private Sub Workbook_Open()

    Dim nr As Long

    With Worksheets("FoglioDiLog")

        nr = .Range("A65536").End(xlUp).Row

        .Cells(nr + 1, 1).Value = ReturnUserName
        .Cells(nr + 1, 2).Value = Format(Date, "mm/dd/yyyy")
        .Cells(nr + 1, 3).Value = Format(Now, "hh:mm:ss")

    End With

End Sub

Avendo preventivamente creato un foglio
chiamato FoglioDiLog, ogni volta che un
utente mi apre il file, viengono scritti:

Nome utente
Data
Ora

Ti metto qui un esempio:

http://www.webalice.it/maurogsc/prova/xlsvbaFoglioDiLog.zip

1-10 of 55