100 Useful VBA Macro Codes In Excel

100 Useful VBA Macro Codes In Excel

What is a Macro Code?

In MS Excel, macro code is a programming code which is written in VBA (Visual Basic for Applications) language.

The idea is to use macro code for automating various activities which you perform manually in Excel, otherwise.

For example, you can use a code to print only a particular range of cells just with a single click instead of selecting the range -> File Tab -> Print -> Print Select -> OK Button.

How to use a Macro Code in Excel

useful-macro-codes-examples-to-use-from-macro-options

  • On the left side in “Project Window”, right click on the name of your workbook and insert a new module.

add-module-to-paste-these-useful-macros-for-excel

 

  • Just paste your code into the module and close it.

use-useful-macro-codes-examples-by-pasting-them-into-vb-editor

 

  • Now, go to your developer tab and click on the macro button.

click-on-visual-basic-editor-before-you-use-these-useful-macros-for-excel

  • It will show you a window with a list of the macros you have in your file from where you can run a macro from that list.

useful-macro-codes-examples-list-from-macro-options

 

List of Top 100 macro Examples (CODES) for VBA beginners

I have added all the codes into specific categories so that you can find your favorite codes quickly. Just read the title and click on it to get the code.

Table of Contents

Basic VBA Macro Codes in Excel

Custom Header & Footer

Sub CustomHeader() Dim myText As String myText = InputBox(“Enter your …

Get Macro Code

Add Header and Footer Date

Sub DateInHeader() With ActiveSheet.PageSetup .LeftHeader = “” .CenterHeader = “&D” …

Get Macro Code

Unmerge Cells

Sub UnmergeCells() Selection.UnMerge End Sub Unmerge Cells code simply uses …

Get Macro Code

Open Calculator

Sub OpenCalculator() Application.ActivateMicrosoftApp Index:=0 End Sub In Windows, there is …

Get Macro Code

Auto Fit Rows

Sub AutoFitRows() Cells.Select Cells.EntireRow.AutoFit End Sub You can use this …

Get Macro Code

Remove Text Wrap

Sub RemoveTextWrap() Range(“A1”).WrapText = False End Sub This code will …

Get Macro Code

Auto Fit Columns

Sub AutoFitColumns() Cells.Select Cells.EntireColumn.AutoFit End Sub Use above Excel macro …

Get Macro Code

Insert Multiple Columns

Sub InsertMultipleColumns() Dim i As Integer Dim j As Integer …

Get Macro Code

Insert Multiple Rows

Sub InsertMultipleRows() Dim i As Integer Dim j As Integer …

Get Macro Code

Add Serial Numbers

Sub AddSerialNumbers() Dim i As Integer On Error GoTo Last …

Get Macro Code

Formatting VBA Macro Codes in Excel

Highlight Greater than Values

Sub HighlightGreaterThanValues() Dim i As Integer i = InputBox(“Enter Greater …

Get Macro Code

Highlight Lower Than Values

Sub HighlightLowerThanValues() Dim i As Integer i = InputBox(“Enter Lower …

Get Macro Code

Highlight Negative Numbers

Sub highlightNegativeNumbers() Dim Rng As Range For Each Rng In …

Get Macro Code

Highlight Specific Text

Sub highlightValue() Dim myStr As String Dim myRg As range …

Get Macro Code

Highlight Alternate Rows in the Selection

Sub highlightAlternateRows() Dim rng As Range For Each rng In …

Get Macro Code

Highlight Cells With Error in the Entire Worksheet

Sub highlightErrors() Dim rng As Range Dim i As Integer …

Get Macro Code

Highlight Cells with Misspelled Words

Sub HighlightMisspelledCells() Dim rng As Range For Each rng In …

Get Macro Code

Highlight Cells with a Specific Text in Worksheet

Sub highlightSpecificValues() Dim rng As range Dim i As Integer …

Get Macro Code

Highlight all Blank Cells Invisible Space

Sub blankWithSpace() Dim rng As Range For Each rng In …

Get Macro Code

Highlight Named Ranges

Sub HighlightRanges() Dim RangeName As Name Dim HighlightRange As Range …

Get Macro Code

Chart VBA Macro Codes in Excel

Add Chart Title

Sub AddChartTitle() Dim i As Variant i = InputBox(“Please enter …

Get Macro Code

Paste Chart as an Image

Sub ConvertChartToPicture() ActiveChart.ChartArea.Copy ActiveSheet.Range(“A1”).Select ActiveSheet.Pictures.Paste.Select End Sub Paste Chart as …

Get Macro Code

Change Chart Type

Sub ChangeChartType() ActiveChart.ChartType = xlColumnClustered End Sub This code will …

Get Macro Code

Formula VBA Macro Codes in Excel

Convert Date into Day

Convert Date into Day Sub date2day() Dim tempCell As Range …

Get Macro Code

Insert Time Range

Sub TimeStamp() Dim i As Integer For i = 1 …

Get Macro Code

Activate R1C1 Reference Style

Sub ActivateR1C1() If Application.ReferenceStyle = xlA1 Then Application.ReferenceStyle = xlR1C1 …

Get Macro Code

Activate A1 Reference Style

Sub ActivateA1() If Application.ReferenceStyle = xlR1C1 Then Application.ReferenceStyle = xlA1 …

Get Macro Code

Reverse Text

Public Function rvrse(ByVal cell As Range) As String rvrse = …

Get Macro Code

Add Insert Degree Symbol in Excel

Sub degreeSymbol( ) Dim rng As Range For Each rng …

Get Macro Code

Remove Characters from a String

Public Function removeFirstC(rng As String, cnt As Long) removeFirstC = …

Get Macro Code

Remove Spaces from Selected Cells

Sub RemoveSpaces() Dim myRange As Range Dim myCell As Range …

Get Macro Code

Word Count from Entire Worksheet

Sub Word_Count_Worksheet() Dim WordCnt As Long Dim rng As Range …

Get Macro Code

Remove Decimals from Numbers

Sub removeDecimals() Dim lnumber As Double Dim lResult As Long …

Get Macro Code

Pivot Table VBA Macro Codes in Excel

Disable/Enable Get Pivot Data

Sub activateGetPivotData() Application.GenerateGetPivotData = True End Sub Sub deactivateGetPivotData() Application.GenerateGetPivotData …

Get Macro Code

Refresh All Pivot Tables

ub vba_referesh_all_pivots() Dim pt As PivotTable For Each pt In …

Get Macro Code

Auto Update Pivot Table Range

Sub UpdatePivotTableRange() Dim Data_Sheet As Worksheet Dim Pivot_Sheet As Worksheet …

Get Macro Code

Hide Pivot Table Subtotals thru VBA

Sub HideSubtotals() Dim pt As PivotTable Dim pf As PivotField …

Get Macro Code

Printing VBA Macro Codes in Excel

Print Custom Pages

Sub printCustomSelection() Dim startpage As Integer Dim endpage As Integer …

Get Macro Code

Print Selection

Sub printSelection() Selection.PrintOut Copies:=1, Collate:=True End Sub Excel Macro code …

Get Macro Code

Print Narrow Margin

Sub printNarrowMargin() With ActiveSheet.PageSetup .LeftMargin = Application .InchesToPoints (0.25) .RightMargin …

Get Macro Code

Print Comments

Print Comments Sub printComments() With ActiveSheet.PageSetup .printComments = xlPrintSheetEnd End …

Get Macro Code

Workbook VBA Macro Codes in Excel

Add Workbook to a Mail Attachment

Sub OpenWorkbookAsAttachment() Application.Dialogs(xlDialogSendMail).Show End Sub Add Workbook to a Mail …

Get Macro Code

Close all Workbooks at Once

Sub CloseAllWorkbooks() Dim wbs As Workbook For Each wbs In …

Get Macro Code

Welcome Message

Sub auto_open() MsgBox _ “Welcome To xlsxtemplates & Thanks for …

Get Macro Code

Count Open Unsaved Workbooks

Count Open Unsaved Workbooks. Sub VisibleWorkbooks() Dim book As Workbook …

Get Macro Code

Active Workbook in an Email

Sub Send_Mail() Dim OutApp As Object Dim OutMail As Object …

Get Macro Code

Copy Active Worksheet into a New Workbook

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

Get Macro Code

Closing Message

Sub auto_close() MsgBox “Bye Bye! Don’t forget to check other …

Get Macro Code

Create a Backup of a Current Workbook

Sub FileBackUp() ThisWorkbook.SaveCopyAs Filename:=ThisWorkbook.Path & _ “” & Format(Date, “mm-dd-yy”) …

Get Macro Code

Worksheet VBA Macro Codes in Excel

Unhide all Rows and Columns

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

Get Macro Code

Save Each Worksheet as a Single PDF

Sub SaveWorkshetAsPDF() Dimws As Worksheet For Each ws In Worksheets …

Get Macro Code

Delete all Blank Worksheets

Sub deleteBlankWorksheets() Dim Ws As Worksheet On Error Resume Next …

Get Macro Code

Sort Worksheets

Sub SortWorksheets() Dim i As Integer Dim j As Integer …

Get Macro Code

Protect all the Cells With Formulas

Sub lockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = …

Get Macro Code

Disable Page Breaks

Sub DisablePageBreaks() Dim wb As Workbook Dim wks As Worksheet …

Get Macro Code

Protect Worksheet

Sub ProtectWS() ActiveSheet.Protect “mypassword”, True, True End Sub If you …

Get Macro Code

UnProtect Worksheet

Sub UnprotectWS() ActiveSheet.Unprotect “mypassword” End Sub UnProtect Worksheet If you …

Get Macro Code

Resize All Charts in a Worksheet

Sub Resize_Charts() Dim i As Integer For i = 1 …

Get Macro Code

Insert Multiple Worksheets

Sub InsertMultipleSheets() Dim i As Integer i = _ InputBox(“Enter …

Get Macro Code

Advance VBA Macro Codes in Excel

Use Goal Seek

Sub GoalSeekVBA() Dim Target As Long On Error GoTo Errorhandler …

Get Macro Code

VBA Code to Search on Google

Sub SearchWindow32() Dim chromePath As String Dim search_string As String …

Get Macro Code

Activate Data Entry Form

Sub DataForm() ActiveSheet.ShowDataForm End Sub There is a default data entry …

Get Macro Code

Insert a Linked Picture

Sub LinkedPicture() Selection.Copy ActiveSheet.Pictures.Paste(Link:=True).Select End Sub How to Insert a …

Get Macro Code

Use Text to Speech

Sub Speak() Selection.Speak End Sub VBA code to Use Text …

Get Macro Code

Convert Range into an Image

Sub PasteAsPicture() Application.CutCopyMode = False Selection.Copy ActiveSheet.Pictures.Paste.Select End Sub How …

Get Macro Code

Create a Table of Content

Sub TableofContent() Dim i As Long On Error Resume Next …

Get Macro Code

Save Selected Range as a PDF

Sub HideSubtotals() Dim pt As PivotTable Dim pf As PivotField …

Get Macro Code

Free Excel Templates

employee payroll information

Employee Payroll Register

As a new employer, I am about to create a …

chore-chart

Chore Chart

If you are a parent, you will probably like to …

Decision Sheet for Engineering

Decision Sheet for Engineering

In the fast-paced and precision-driven field of engineering, making informed …

Break-Even-Analysis

Break Even Analysis

Introducing Your Ultimate Guide to Break-even Analysis  Template in Excel …

We Create Your Life Easy With Our Free Excel Templates & Spreadsheet Dashboards

Use free Excel Templates, Tools and Dashboards to create and draft a professional looking dashboards and computation sheet for your personal and business use. You can Explore Calendars, Activity Planners, Invoice Templates, Sales Forecast Sheets, Budget templates and various business information delivering Templates in Excel and Spreadsheet at XLSX Templates. 

These templates, dashboards and tools are useful in various occasions. You can also customize these templates as per your requirement. Modification of fields and data source helps in making the template more relevant.

Learn Microsoft Excel : Blogs and Articles

Learn about various tips and tricks in Microsoft Excel and Spreadsheet. Create best templates and dashboards using free tricks and tutorials in excel and spreadsheet. These tutorial posts are useful for everyone who wants to master the skills in excel and spreadsheet.