Home

 

Joy E. Daniels     ♦ Programming Reporting Support

Excel VBA Code

 

Services
Clients
Products
VBA Code
Shortcut Keys
Contact

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