VBA CODE FOR EXCEL

code, which use for macro in excel

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

 source

Facebook Comments