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?
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?
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?
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 13 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 leveraging geographically dispersed team members.