Handy Bits of Excel VBA Code
|
|
Adjust Column Width to fit Cell Text | |
Columns ("A:B").Entireolumn.AutoFit | |
Deactivate the Copy "Martching Ants" Marquee | |
Application.CutCopyMode = False | |
Insert Blank Row or Blank Column | |
ActiveCell.EntireRow.Select | |
Selection.Insert Shift:=xlDown | |
or | |
ActiveCell.EntireColumn.Select | |
Selection.Insert Shift:=xlToRight | |
Move to last cell at bottom of data block | |
Selection.End(xlDown).Select | |
Paste Values Only | |
Selection.PasteSpecial Paste:=xlValues | |
Protect or Unprotect Active Worksheet | |
ActiveSheet.Protect Password:="PASSWORD" | |
or | |
ActiveSheet.Unprotect Password:="PASSWORD" | |
Resize Range Examples | |
To resize the current selection by 2 rows and 4 columns. | |
Selection.Resize(Selection.Rows.Count + 2, Selection.Columns.Count + 4).Select | |
To resize the current selection by 5 rows | |
Selection.Resize(Selection.Rows.Count + 5, Selection.Columns.Count).Select | |
To resize the current selection by 3 columns. | |
Selection.Resize(Selection.Rows.Count, Selection.Columns.Count + 3).Select | |
Save and Close the Active Workbook | |
ActiveWorkbook.Save | |
ActiveWindow.Close | |
Select a Contiguous Block of Data | |
Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select | |
Select Entire Row or Entire Column (based on current cursor position) | |
ActiveCell.EntireRow.Select | |
or | |
ActiveCell.EntireColumn.Select | |
Select Next Blank Cell at Bottom of Data Block | |
Selection.End(xlDown).Select | |
ActiveCell.Offset(1,0).Range("A1").Select | |
Select Next or Previous Sheet in Workbook | |
ActiveSheet.Next.Select | |
or | |
ActiveSheet.Previous.Select |