Excel VBA – Frequently Used Code Snippets

If you happen to occasionally work on Excel VBA, you would forget various functions and for a small but advanced VBA, you would have to use Google for each line of code. Here is all at one place.

How to turn off Excel alerts?

Application.DisplayAlerts = False

How to move once cell down/up/left/right?

ActiveCell.Offset(1, 0).Select

How to add new worksheet with desired name?

Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
Sheets(Sheets.Count).Name = "DesiredName"

How to set value in a particular cell?

Range("B1").Select
ActiveCell.FormulaR1C1 = "My Value"

How to get the path of current workbook?

Dim path As String
path = ActiveWorkbook.path

How to loop through all the files/subfolders?

Dim objFso As Object
Dim objFolder As Object
Dim colSubFolder As Object
Dim objSubFolder As Object
Dim objFile As Object
Dim ext As String
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder("C:\Temp\MyCustomPath\")
Set colSubFolder = objFolder.Subfolders
For Each objSubFolder In colSubFolder
	For Each objFile In objSubFolder.Files
		ext = objFso.GetExtensionName(objFile.path)
	Next
Next

How to select last cell?

Selection.SpecialCells(xlCellTypeLastCell).Select

How to imitate Ctrl + Shift + arrow key selection?

Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlUp)).Select

How to copy selection?

Selection.Copy

How to paste selection?

ActiveSheet.Paste

How to paste only value?

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

How to close workbook without saving?

wbook.Close savechanges:=False

How to insert blank column?

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove ' This inserts blank column at column A

How to cut column J and paste at column C?

Columns("J:J").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight