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).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
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 *