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

VBA Open Excel File – Explained with Examples!

VBA code to open Excel File will help you to open Excel Workbook using VBA. VBA open excel file Examples to show you use of Workbook.Open method in Excel VBA 2003, 2007, 2010, 2013.

VBA Code to Open an Excel File using Workbooks.Open Method

We can open Excel Workbook using Workbooks.Open Method. Following are the VBA Examples and syntax of VBA Code to Open an Excel File.

VBA Code to Open an Excel File using Workbooks.Open Method: Syntax

Here is the syntax to Open an Excel File using VBA. Here we need to pass the Workbook path to open.


Workbooks.Open("C:\temp\test.xlsx")

VBA Code to Open an Excel File using Workbooks.Open Method: Examples

The following Excel VBA example will open the test.xlsx file in the C:\temp folder. Here we are opening the workbook and setting to an object. This will help us to re use the open workbook in the program.

1 2 3 4

Sub sbVBA_To_Open_Workbook()     Dim wb As Workbook     Set wb = Workbooks.Open("C:\temp\test.xlsx") "Excel is fun" End Sub

VBA Code to Open an Excel File Explained:
‘Starting procedure to write VBA code to open excel file
Sub sbVBA_To_Open_Workbook()

‘Declaring the wb variable as workbook
Dim wb As Workbook

‘Opening a workbook and setting to the wb object for further use
Set wb = Workbooks.Open(“C:\temp\test.xlsx”)

‘Ending the sub procedure
End Sub

VBAopen excel file: Why we are using an Object

This is is the best approach to opening and assigning workbook to an object. This will help us to re use the Opened workbook and deal with its worksheets, ranges and other objects. The following example will show you how to access the different examples of opened workbook by setting and assigning to an object.

Excel is Fun.

The below VBA Code example will get the Name of the Opened Workbook
We are using the Workbook.Name property to get the workbook name of the opened workbook.

1 2 3 4 5 6 7 8

Sub sbVBA_To_Open_WorkbookName()     Dim wb As Workbook           Set wb = Workbooks.Open("C:\temp\test.xlsx")           'This will return the workbook name     MsgBox wb.Name End sub

The below VBA Code will get the count of worksheets in the Opened Workbook
We are using the Worksheets.Count property of workbook to get the number of worksheets in the opened workbook.

1 2 3 4 5 6 7 8

Sub sbVBA_To_Open_Workbook_Worksheets_Count()     Dim wb As Workbook           Set wb = Workbooks.Open("C:\temp\test.xlsx")           'This will return number of worksheets in the workbook     MsgBox wb.Worksheets.Count End sub

The below VBA Code example will get the first worksheet Name of the Opened Workbook
We are using the Worksheet.Name property of workbook to get the name of the of worksheets in the opened workbook.

1 2 3 4 5 6 7 8

Sub sbVBA_To_Open_Workbook_Worksheets_Count()     Dim wb As Workbook           Set wb = Workbooks.Open("C:\temp\test.xlsx")           'This will return the first sheet name of the workbook     MsgBox wb.Sheets(1).Name End sub

The below VBA Code example will get the Range C2 value of the Worksheet “Main” of the Opened Workbook
We are using the Worksheet.Range object of workbook to get the Range value of the worksheets in the opened workbook.

1 2 3 4 5 6 7 8

Sub sbVBA_To_Open_Workbook_Worksheets_Count()     Dim wb As Workbook           Set wb = Workbooks.Open("C:\temp\test.xlsx")           'This will return the Range C2 value of the worksheet "Main"     MsgBox wb.Sheets("Main").Range("C2") End sub I will love your feedback. Please share.Excel is Fun