Joy E. Daniels     ♦ Programming ♦ Reporting ♦ Support ♦

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