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