VBA CHEAT SHEETS - Automate Excel

1 DescriptionVBA CodeActivate CellRange( B3 ).ActivateCells(3,2).ActivateSelect RangeRange( a1:a3 ).SelectRange(Range( a1 ), Range( a3 )).SelectRange(Cells(1, 1), Cells(3, 1)).SelectResizeRange( B3 ).Resize(2, 2).SelectOffsetRange( B3 ).Offset(2, 2).SelectCopyRange( A1:B3 ).Copy Range( D1 )CutRange( A1:B3 ).Cut Range( D1 )DeleteRange( A1:B3 ).DeleteRange( A1:B3 ).Delete shift:=xlShiftToLeftClearRange( A1:A3 ).ClearRange( A1:A3 ).ClearContentsRange( A1:A3 ).ClearFormatCountRange( A1:A3 ).CountSet to VariableDim rng as RangeSet rng = Range( A1 )Merge/UnMergeRange( A1:A3 ).MergeRange( A1:A3 ).UnMergeLoop Through CellsDim cell As RangeFor Each cell In Range( A1:C3 )MsgBox cellCELLS & RANGESVBA CHEAT SHEETSD escriptionVBA CodeActivate by Tab NameSheets( Input ).

2 ActivateActivate by VBA Code by Index PositionSheets(1).ActivateNext ActiveSheetMsgBox SheetSheets( Input ).SelectSet to VariableDim ws as WorksheetSet ws = ActiveSheetName / = NewName Add Sheet and = NewSheet Add Sheet to VariableDim ws As WorksheetSet ws = SheetSheets( Sheet1 ).Copy Before:= SHEETS ( Sheet2 )Hide SheetSheets( Sheet1 ).visible = FalseorSheets( Sheet1 ).visible = xlSheetHiddenUnhide SheetSheets( Sheet1 ).Visible = TrueorSheets( Sheet1 ).Visible = xlSheetVisibleVery Hide SheetSheets( Sheet1 ).Visible = xlSheetVeryHiddenDelete SheetSheets( Sheet1 ).DeleteClear SheetSheets( Sheet1 ). (No Password) SHEETS ( Sheet1 ).UnprotectUnprotect (Pass-word) SHEETS ( Sheet1 ).

3 Unprotect Password Protect (No Password) SHEETS ( Sheet1 ).ProtectProtect (Pass-word) SHEETS ( Sheet1 ).Protect Password Protect but Allow VBA AccessSheets( Sheet1 ).Protect UserInterfaceOnly:=TrueDescriptionVBA CodeActivateWorkbooks( Book1 ).ActivateActivate First OpenedWorkbooks(1).ActivateActivate Last OpenedWorkbooks( ).ActivateGet activate WorkbookMsgBox ThisWorkbook (containing VBA Code)MsgBox to VariableDim wb As WorkbookSet wb = ( C:\ )Open to VariableDim wb As WorkbookSet wb = ( C:\ )CloseWorkbooks( Book1 ).Close SaveChanges:=FalseWorkbooks( Book1 ).Close SaveChanges:=TrueSaveWorkbooks( Book1 ).SaveSave AsWorkbooks( Book1 ).SaveAs strFileNameProtect/UnprotectWorkbooks(1) .

4 Protect password Workbooks(1).Unprotect password Set to Variable Dim wb as WorkbookSet wb = Workbooks( Book1 )Loop Through All Workbook in WorkbooksDim wb As WorkbookFor Each wb In WorkbooksMsgBox wbCheck ExistsIf Dir( C:\ ) = ThenMsgBox File does not exist. EndIfCopy ClosedFileCopy C:\ , C:\ SHEETSWORKBOOKSD escriptionVBA CodeActivateRows(1).ActivateRows( 1:1 ).ActivateRange( a1 ). / WidthRange( A1 ). = 30 DeleteRange( A1 ). ( A1 ). ( A1 ). lRow as longlRow = Cells( , 1).End(xlUp).RowCopyRange( 1:1 ).Copy Range( 5:5 )InsertRange( 1:1 ).CopyRange( 5:5 ).InsertROWSD escriptionVBA CodeActivateColumns(1).ActivateColumns( a:a ).ActivateRange( a1 ).

5 / WidthRange( A1 ).EntireColumn. ColumnWidth = 30 DeleteRange( A1 ). ( A1 ). ( A1 ). lCol as longlCol = Cells(1, ).End (xlToLeft).ColumnCopyRange( A:A ).Copy Range( E:E )InsertRange( A:A ).CopyRange( E:E ).InsertDescriptionVBA CodeOn Error Stop code and display errorOn Error Goto 0On Error Skip error and continue runningOn Error Resume NextOn Error Go to a line of code [Label]On Error Goto [Label]Clears (Resets) ErrorOn Error GoTo 1 Show Error numberMsgBox Description of errorMsgBox to gen-erate own CodeCopy FileFileCopy C:\test\ , C:\test\ Delete FileKill C:\test\ Make FolderMkDir C:\test\ Delete All Files From FolderKill C:\test\ & *.

6 * Delete FolderKill C:\test\ & *.* RmDir C:\test\ Current DirectorystrPath = CurDir()ThisWorkbook PathstrPath = Through All Files in FolderstrFile = Dir( C:\test & \* )Do While Len(strFile) > strFilestrFile = DirLoopFILESAutoMacro: VBA Add-in with Hundreds of Ready-To-Use Code Examples, Code Generators, and much more!Learn CodeCreateDim arr(1 To 3) As Variantarr(1) = one arr(2) = two arr(3) = three Create From ExcelDim arr(1 To 3) As VariantDim cell As Range, i As Integer i = LBound(arr)For Each cell In Range( A1:A3 )i = i + 1arr(i) = cellRead All ItemsDim i as LongFori = LBound(arr) To UBound(arr)MsgBox arr(i)Next iEraseErase arrArray to StringDim sName As StringsName = Join(arr.)

7 Increase SizeReDim Preserve arr(0 To 100)Set Valuearr(1) = 22 ARRAYSD escriptionVBA CodeScreen = = TrueDisplay = = = = TrueEnable Cancel = = xlInterruptText Compare Ignore CaseOption Compare TextRequire Variable DeclarationOption ExplicitAutomatic = = xlAutomaticBackground Error = = TrueDisplay Formula = = TrueFreeze = = TrueFull Screen = = TruePageBreak = = xlNormalViewDisplay Scroll BarsWith = = FalseEnd WithWith = = TrueEnd WithDisplay Status = = TrueStatus Bar = I m working Now!!! = FalseDisplay Work-book = = = App = AutomateExcel Model = 80 DescriptionVBA CodeRequired ReferenceTools > References > Microsoft Scripting RuntimeCreateDim dict As New Create From ExcelDim dict As New cell As RangeDim key As IntegerFor Each cell In Range( A1.)

8 A10 )key = key + key, cellAdd Key , Value Change Valuedict( Key ) = Value Get ValueMsgBox dict( Key )Check For ValueIf ( Key ) ThenMsgBox Exists End IfRemove ( Key )Remove All Through ItemsDim key As VariantFor Each key In key, dict(key)Next keyCount Key Case = vbBinaryCompareMake Key Case = vbTextCompareSETTINGSDICTIONARIESD escriptionVBA CodeCreateDim coll As New one two Create From ExcelDim coll As New CollectionDim cell As RangeFor Each cell In Range( A1:A2 ) cellAdd Value Add Item Value , Before:=1 Add Item Value , After:=1 Read ItemMsgBox coll (1)Count All ItemsDim item As VariantFor Each item In collMsgBox itemNext itemRemove (1)Remove All ItemsSet coll = New CollectionCOLLECTIONSAutoMacro: VBA Add-in with Hundreds of Ready-To-Use Code Examples, Code Generators, and much more!

9 Learn