|
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 |