Excel VBA – Frequently used code snippets

By | July 11, 2012

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).Name = "DesiredName"

How to set value in a particular cell?

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)

How to select last cell?


How to imitate Ctrl + Shift + arrow key selection?

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

How to copy selection?


How to paste selection?


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?

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove ' This inserts blank column at column A

How to cut column J and paste at column C?

Selection.Insert Shift:=xlToRight
Vishal Monpara is a full stack Solution Developer/Architect with 12 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1’s and 0’s from geographically dispersed hard disks to geographically dispersed user’s mind leveraging geographically dispersed team members.

One thought on “Excel VBA – Frequently used code snippets

Leave a Reply

Your email address will not be published. Required fields are marked *