working on it ...

## Filters

Sort by

Found 13 snippets

### 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```

### Excel VBA Merge cells with same value

Merge cells in different columns with the same value in an ordered list
```Sub merge_cells()

'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
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
iFirstColumn = i
End If
Next i

End Sub
```

### 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            ```

### 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, "/", "-")```

### 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)
```

### 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"`

### 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
```

### 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```

### 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```

### 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