Description VBA Code
Activate Cell Range(“B3”).Activate
Cells(3,2).Activate
Select Range
Range(“a1:a3”).Select
Range(Range(“a1”), Range(“a3”)).Select
Range(Cells(1, 1), Cells(3, 1)).Select
Resize Range(“B3”).Resize(2, 2).Select
Offset Range(“B3”).Offset(2, 2).Select
Copy Range(“A1:B3”).Copy Range(“D1”)
Cut Range(“A1:B3”).Cut Range(“D1”)
Delete Range(“A1:B3”).Delete
Range(“A1:B3”).Delete shift:=xlShiftToLeft
Clear
Range(“A1:A3”).Clear
Range(“A1:A3”).ClearContents
Range(“A1:A3”).ClearFormat
Count Range(“A1:A3”).Count
Set to Variable Dim rng as Range
Set rng = Range(“A1”)
Merge/UnMerge Range(“A1:A3”).Merge
Range(“A1:A3”).UnMerge
Loop Through
Cells
Dim cell As Range
For Each cell In Range(“A1:C3”)
MsgBox cell.Value
Next cell
CELLS & RANGES
VBA CHEAT SHEETS
Description VBA Code
Activate by Tab
Name Sheets(“Input”).Activate
Activate by VBA
Code Name Sheet1.Activate
Activate by Index
Position Sheets(1).Activate
Next Sheet ActiveSheet.Next.Activate
Get ActiveSheet MsgBox ActiveSheet.Name
Select Sheet Sheets(“Input”).Select
Set to Variable Dim ws as Worksheet
Set ws = ActiveSheet
Name / Rename ActiveSheet.Name = “NewName”
Add Sheet Sheets.Add
Add Sheet and
Name Sheets.Add.Name = “NewSheet”
Add Sheet to
Variable
Dim ws As Worksheet
Set ws = Sheets.Add
Copy Sheet Sheets(“Sheet1”).Copy
Before:=Sheets(“Sheet2”)
Hide Sheet
Sheets(“Sheet1”).visible = False
or
Sheets(“Sheet1”).visible = xlSheetHidden
Unhide Sheet
Sheets(“Sheet1”).Visible = True
or
Sheets(“Sheet1”).Visible = xlSheetVisible
Very Hide Sheet Sheets(“Sheet1”).Visible =
xlSheetVeryHidden
Delete Sheet Sheets(“Sheet1”).Delete
Clear Sheet Sheets(“Sheet1”).Cells.Clear
Unprotect (No
Password) Sheets(“Sheet1”).Unprotect
Unprotect (Pass-
word) Sheets(“Sheet1”).Unprotect “Password”
Protect (No
Password) Sheets(“Sheet1”).Protect
Protect (Pass-
word) Sheets(“Sheet1”).Protect “Password”
Protect but Allow
VBA Access
Sheets(“Sheet1”).Protect
UserInterfaceOnly:=True
Description VBA Code
Activate Workbooks(“Book1”).Activate
Activate First
Opened Workbooks(1).Activate
Activate Last
Opened Workbooks(Workbooks.Count).Activate
Get activate
Workbook MsgBox ActiveWorkbook.Name
Get
ThisWorkbook
(containing VBA
Code)
MsgBox ThisWorkbook.Name
Add Workbooks.Add
Add to Variable Dim wb As Workbook
Set wb = Workbooks.Add
Open Workbooks.Open(“C:\example.xlsm”)
Open to Variable
Dim wb As Workbook
Set wb = Workbooks.Open(“C:\example.
xlsm”)
Close
Workbooks(“Book1”).Close
SaveChanges:=False
Workbooks(“Book1”).Close
SaveChanges:=True
Save Workbooks(“Book1”).Save
Save As Workbooks(“Book1”).SaveAs strFileName
Protect/
Unprotect
Workbooks(1).Protect “password”
Workbooks(1).Unprotect “password”
Set to
Variable
Dim wb as Workbook
Set wb = Workbooks(“Book1”)
Loop Through
All Workbook in
Workbooks
Dim wb As Workbook
For Each wb In Workbooks
MsgBox wb.Name
Next wb
Check Exists
If Dir(“C:\Book1.xlsx”) = “” Then
MsgBox “File does not exist.”
EndIf
Copy Closed FileCopy “C:\file1.xlsx”,”C:\file2.xlsx”
SHEETS WORKBOOKS
Description VBA Code
Activate
Rows(1).Activate
Rows(“1:1”).Activate
Range(“a1”).EntireRow.Activate
Height / Width Range(“A1”).EntireRow.RowHeight = 30
Delete Range(“A1”).EntireRow.Delete
Count Range(“A1”).Rows.Count
Insert Range(“A1”).EntireRow.Inser
Last
dim lRow as long
lRow = Cells(Rows.Count, 1).End(xlUp).
Row
Copy Range(“1:1”).Copy Range(“5:5”)
Insert Range(“1:1”).Copy
Range(“5:5”).Insert
ROWS
Description VBA Code
Activate
Columns(1).Activate
Columns(“a:a”).Activate
Range(“a1”).EntireColumn.Activate
Height / Width Range(“A1”).EntireColumn.
ColumnWidth = 30
Delete Range(“A1”).EntireColumn.Delete
Count Range(“A1”).Columns.Count
Insert Range(“A1”).EntireColumn.Insert
Last
dim lCol as long
lCol = Cells(1, Columns.Count).End
(xlToLeft).Column
Copy Range(“A:A”).Copy Range(“E:E”)
Insert Range(“A:A”).Copy
Range(“E:E”).Insert
Description VBA Code
On Error – Stop
code and display
error
On Error Goto 0
On Error – Skip
error and
continue running
On Error Resume Next
On Error – Go
to a line of code
[Label]
On Error Goto [Label]
Clears (Resets)
Error On Error GoTo –1
Show Error
number MsgBox Err.Number
Show Description
of error MsgBox Err.Description
Function to gen-
erate own error Err.Raise
COLUMNS
ERRORS
Description VBA Code
Copy File FileCopy “C:\test\test_old.xlsx”, “C:\test\
test_new.xlsx”
Delete File Kill “C:\test\example.xlsx”
Make Folder MkDir “C:\test\”
Delete All Files
From Folder Kill “C:\test\” & “*.*”
Delete Folder Kill “C:\test\” & “*.*”
RmDir “C:\test\”
Current Directory strPath = CurDir()
ThisWorkbook
Path strPath = ThisWorkbook.Path
Loop Through All
Files in Folder
strFile = Dir(“C:\test” & “\*”)
Do While Len(strFile) > 0
Debug.Print strFile
strFile = Dir
Loop
FILES
