working on it ...

Filters

snippets
13
followers
14
Published by fabio.nosenzo

MS Excel VBA

All the snippets that can be used in MS Excel VBA programming
Sort by

Found 13 snippets

    public by fabio.nosenzo  2678  3  6  -1

    Count unique values excel formula

    General formula to calculate unique values with MS Excel
    //replace the values "*****" with your data range
    
    SUM(IF(FREQUENCY(IF(LEN(*****)>0,MATCH(*****,*****,0),""), IF(LEN(*****)>0,MATCH(*****,*****,0),""))>0,1))
    
    // remember to insert formula as an array formula with ctrl+shift+enter

    public by fabio.nosenzo  3293  14  6  1

    Excel VBA Merge cells with same value

    Merge cells in different columns with the same value in an ordered list
    Sub merge_cells()
    Application.DisplayAlerts = False
        
        'row to be avaluated
        iRow = 1
        'number of columns to be evaluated
        iNumberOfColumns = 12
        'first Column to be evaluated
        iFirstColumn = 4
        For i = iFirstColumn + 1 To iNumberOfColumns + iFirstColumn + 1
            If Cells(iRow, i) <> Cells(iRow, i - 1) Then
            iLastRow = i - 1
            'get first column letter
            vArr = Split(Cells(1, iFirstColumn).Address(True, False), "$")
            sFirstColumn = vArr(0)
            'get last column letter
            vArr = Split(Cells(1, i - 1).Address(True, False), "$")
            sLastColumn = vArr(0)
            sRange = sFirstColumn & iRow & ":" & sLastColumn & iRow
            Range(sRange).Select
                With Selection
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                    .WrapText = True
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = False
                End With
            Selection.Merge
            iFirstColumn = i
            End If
        Next i
          
    Application.DisplayAlerts = True
    End Sub
    

    public by fabio.nosenzo  3177  3  6  2

    Get column letter Excel VBA

    Given the column number, this function returns the letter(s) corrisponding to the excel column. Thanks to brettdj
    Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
    End Function
    
    'test the code
    
    Sub Test()
    MsgBox Col_Letter(100)
    End Sub            

    public by fabio.nosenzo  3282  0  6  1

    Replace Function MS Excel VBA

    Replace a string with another within a string
    ' Create a file path with date
    sPath = "C:\myfile - " & Date
    ' / char is not admitted
    ' replace / with -
    sPath = Replace(sPath, "/", "-")

    public by fabio.nosenzo  3576  1  6  0

    Manage errors excel VBA

    Command to be used as Iferror() formula
    ' example with vlookup formula
    
            sVlookup = Application.VLookup(Cells(i, j), Range("A1:C101"), 2, False)
            If IsError(sVlookup) Then sVlookup = "not found"
                            

    public by fabio.nosenzo  2586  2  6  2

    VBA Excel - Add worksheet with name

    How to add a worksheet at the and of a workbook with a specific name
    Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = "My New Worksheet"

    public by fabio.nosenzo  6012  3  7  0

    Screen update disable in Excel VBA

    How to disable and enable screen update in Excel VBA, speeding up macro execution
    ' disable screen updating
    Application.ScreenUpdating = False
    
    ' enable screen updating
    Application.ScreenUpdating = True
    

    public by fabio.nosenzo  3219  0  6  0

    Manage Workbooks Excel VBA

    Useful code to manage workbooks in MS Excel VBA
    ' how to open a workbook
    
    'how to activate a workbook 
    Workbooks(ThisWorkbook.Name).Activate
    
    'how to activate the workbook with the macro that is running
    Workbooks(ThisWorkbook.Name).Activate

    public by fabio.nosenzo  6516  1  7  1

    Get Current Date In VBA

    How to use date function for getting date in VBA Excel. In VBA you can't use today() function but you can use date ()
    ' In VBA you can't use today() function
    ' instead you have to use date ()
    
    'get today date:
    Cells (1,1) = Date
    
    'get yesterday date:
    Cells (1,1) = Date - 1

    public by fabio.nosenzo  215137  1  6  0

    Concatenate excel values with zero padding

    How to concatenate excel values with padding of the second number with correct number of zeros
    'padding with 3 zeros
    
    =IF(B2<10;A2&"000"&B2;A2&"00"&B2)
    • Public Snippets
    • Channels Snippets