Daily Useful VBA Codes

Description	VBA Code
Selecting	'==Select 3 rows down, 2 columns right: ActiveCell.Offset(3, 2).Select '==Select from active cell to last row of list: Range(Selection, Selection.End(xlDown)).Select '==Select from current cell to last column of list: Range(Selection, Selection.End(xlToRight)).Select '==Select last cell of worksheet: Selection.SpecialCells(xlLastCell).Select
Pasting	'==Paste formula value, not formula: Range("A3").Copy Range("D26").PasteSpecial Paste:=xlValues '==Paste into a cell and move its original contents to the next cell: Selection.Insert Shift:=xlToRight
Columns and Rows	'==Hide a column: Selection.EntireColumn.Hidden = True '==Insert a column: Columns("N:N").Insert '==Delete columns: Columns("B:E").EntireColumn.Delete '==Insert a new row at current cell: Selection.EntireRow.Insert '==Delete row of current cell: Selection.EntireRow.Delete '==Set column width: Selection.EntireColumn.ColumnWidth = 10 '==Set row height: Selection.RowHeight = 26.25 '==Set row height to size of contents: Selection.Rows.AutoFit
Cell Formatting	'==Text not wrapped: Selection.WrapText = False '==Remove color: Selection.Interior.ColorIndex = xlNone '==Set font size: Selection.Font.Size = 8 '==Date and time format: Selection.NumberFormat = "mm-dd-yyyy hh:mm AM/PM" '==Number format with comma: Selection.NumberFormat = "#,##0" '==Left aligned: Selection.HorizontalAlignment = xlLeft '==Bottom aligned: Selection.VerticalAlignment = xlBottom '==Indented text: Selection.IndentLevel = 3 '==Delete contents but not formatting: Selection.ClearContents '==Delete contents and formatting: Selection.Clear
Display	'==Hide activity while macro runs: Application.ScreenUpdating = False '==Turn off automatic alerts: Application.DisplayAlerts = False '==Freeze  panes: ActiveWindow.FreezePanes = True '==Show how long macro runs: Dim strTime1 as String, strTime2 as String strTime1 = Format(Now(), "mm-dd-yyyy hh:MM:ss") [put other macro code here] strTime2 = Format(Now(), "mm-dd-yyyy hh:MM:ss") MsgBox "Elapsed Time = " & DateDiff("n", strTime1, strTime2)
Range Names	'==Add a range name: ActiveWorkbook.Names.Add Name:="Groups", RefersTo:=Selection '==Go to a named range: Range("Groups").Select '==Delete all range names in workbook: Dim n as Object For Each n In ActiveWorkbook.Names          n.Delete Next
Path/File Name	'==Insert path/file name into a cell: ActiveCell.Value = ActiveWorkbook.FullName '==Insert path/file name into a footer: ActiveSheet.PageSetup.CenterFooter = ActiveWorkbook.FullName
Pivot Tables	'===Remove unused items in pivot tables when data has changed: Dim pt As PivotTable, ws As Worksheet   For Each ws In ActiveWorkbook.Worksheets           For Each pt In ws.PivotTables                       pt.PivotCache.MissingItemsLimit =                         xlMissingItemsNone           Next pt  Next ws
Worksheets	'==Add date to title of each worksheet: Dim sht As Worksheet For Each sht In ActiveWorkbook.Worksheets             sht.Select             Range("A1").Value = Range("A1").Value & "                through " & strDate Next sht