VBA Tips & Tricks

Hello friends!! We’ll be learning few quick and very helpful Excel VBA tips & tricks. These few VBA tips will help you to save lot of time.

Create New Workbook

Use this VBA code to quickly add new sheet to the workbook

Sub Add ()
     Workbooks.Add
 End Sub

Add New sheet

Use this VBA code to quickly add new sheet to the workbook

Sub Add ()
     Sheets.Add
 End Sub

Hide Sheet

Use this VBA code to quickly hide sheet

Worksheets("Sheet1").visible = False

Merge Cells

You can merge cells in VBA using the Range.Merge method to merge cells

Range("A1:C1").Merge

VBA Clear

VBA Clear will clear all cell properties from a cell

Range("C2").Clear

VBA Clear Formatting will clear cell formatting use ClearFormats

Range("C2").ClearFormats

VBA ClearContents clears only the contents of cells (cell values / text). It does not clear formatting, comments, or anything else.

Range("C2").ClearContents

Auto Fit Rows

You can use this VBA code to auto-fit all the rows in a worksheet. Using this code it will select all the cells in your worksheet and instantly auto-fit all the rows.

Sub AutoFitRows()
Cells.Select
Cells.EntireRow.AutoFit
End Sub

Auto Fit Columns

You can use this VBA code to auto-fit all the columns in a worksheet. Using this code it will select all the cells in your worksheet and instantly auto-fit all the columns.

Sub AutoFitColumns()
Cells.Select
Cells.EntireColumn.AutoFit
End Sub

Un-hide Rows and Columns

Use this VBA code to un-hide all the rows and columns in a single go

Sub UnhideRowsColumns()
Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False
End Sub

Copy Active Worksheet into a New Workbook

Copy your active worksheet in a new workbook using the following VBA code

Sub CopyWorksheetToNewWorkbook()
ThisWorkbook.ActiveSheet.Copy _
Before:=Workbooks.Add.Worksheets(1)
End Sub

Text to Speech in Excel VBA

VBA has inbuilt function to convert text to speech. To convert selected cell’s text to speech use the following small VBA code.

Sub Speak()
Selection.Speak
End Sub

Keep visiting Analytics Tuts for more tutorials.

Thanks for reading! Comment your suggestions and queries