Top 100 Useful Excel Macro [VBA] Codes Examples

Macro codes can save you a ton of time.

You can automate small as well as heavy tasks with VBA codes.

And do you know with the help of macros, you can break all the limitations of Excel which you think Excel has?

So today, I have listed some of the useful codes examples to help you become more productive in your day to day work.

You can use these codes even if you haven't used VBA before that.

All you have to do just paste these codes in your VBA editor.

Don't Forget: Make sure to download this free PDF checklist where I’ve added all these codes (+ two more E-books) for you which you can use to refer them any time in future.

  • `Go to your developer tab and click on "Visual Basic".
click on visual basic editor before you use these useful macros for excel
  • 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 codes 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.
useful-macro-codes-examples-to-use-from-macro-options
  • It will show you a window with a list of the macros you have in your file. And, you can run a macro from that list.
useful macro codes examples list from macro options

And now, let's explore these codes.

...a list of top 100 macro codes for VBA beginners

I’ve added all the codes into specific categories so you can find your favorite codes quickly.​

Just read the title and click on it to get the code.

Important: This is my Ultimate Code Vault which I update on monthly basis with new codes. It would be great if you bookmark this page and keep on visiting to new codes every time.

(A) Basic Codes

These VBA codes will help you to perform some basic tasks in a flash which you frequently do in your spreadsheets.

1. Add Serial Numbers

2. Insert Multiple Columns

3. Insert Multiple Rows

4. Auto Fit Columns

5. Auto Fit Rows

6. Remove Text Wrap

7. Unmerge Cells

8. Open Calculator

9. Add Header/Footer Date

10. Custom Header/Footer

(B) Formatting Codes

These VBA codes will help you to format cells and ranges using some specific criteria and conditions.

11. Highlight Duplicates from Selection

12. Highlight the Active Row and Column

13. Highlight Top 10 Values

14. Highlight Named Ranges

15. Highlight Greater than Values

16. Highlight Lower Than Values

17. Highlight Negative Numbers

18. Highlight Specific Text

19. Highlight Cells with Comments

20. Highlight Alternate Rows in the Selection

21. Highlight Cells with Misspelled Words

22. Count/Highlight Cells With Error in the Entire Worksheet

23. Count/Highlight Cells With A Specific In Entire Worksheet

24. Highlight all the Cells in a Worksheet which are Blank but have an Invisible Space

25. Highlight Max Value In The Range

26. Highlight Min Value In The Range

27. Highlight Unique Values

28. Highlight Difference in Columns

29. Highlight Difference in Rows

(C) Printing Codes

These macro codes will help you to automate some printing tasks which can further save you a ton of time. 

30. Print Comments

31. Print Narrow Margin

32. Print Selection

33. Print Custom Pages

(D) Worksheet Codes

These macro codes will help you to control and manage worksheets in an easy way and save your a lot of time.

34. Hide all but the Active Worksheet

35. Unhide all Hidden Worksheets

36. Delete all but the Active Worksheet

37. Protect all Worksheets Instantly

38. Resize All Charts in a Worksheet

39. Insert Multiple Worksheets

40. Protect Worksheet

41. Un-Protect Worksheet

42. Sort Worksheets

43. Protect all the Cells With Formulas

44. Delete all Blank Worksheets

45. Unhide all Rows and Columns

46. Save Each Worksheet as a Single PDF

47. Disable Page Breaks

(E) Workbook Codes

These codes will help you to perform workbook level tasks in an easy way and with minimum efforts. 

48. Create a Backup of a Current Workbook

49. Close all Workbooks at Once

50. Copy Active Worksheet into a New Workbook

51. Active Workbook in an Email

52. Add Workbook to a Mail Attachment

53. Welcome Message

54. Closing Message

55. Count Open Unsaved Workbooks

(F). Pivot Table Codes

These codes will help you to manage and make some changes in pivot tables in a flash.

56. Hide Pivot Table Subtotals

57. Refresh All Pivot Tables

58. Create a Pivot Table

59. Auto Update Pivot Table Range

60. Disable/Enable Get Pivot Data

(G). Charts Codes

Use these VBA codes to manage charts in Excel and save your lot of time. 

61. Change Chart Type

62. Paste Chart as an Image

63. Add Chart Title

(H) Advanced Codes

Some of the codes which you can use to preform advanced task in your spreadsheets.

64. Save Selected Range as a PDF

65. Create a Table of Content

66. Convert Range into an Image

67. Insert a Linked Picture

68. Use Text to Speech

69. Activate Data Entry Form

70. Use Goal Seek

71. VBA Code to Search on Google

(I) Formula Codes

These codes will help you to calculate or get results which often you do with worksheet functions and formulas.

72. Convert all Formulas into Values

73. Remove Spaces from Selected Cells

74. Remove Characters from a String

75. Add Insert Degree Symbol in Excel

76. Reverse Text

77. Activate R1C1 Reference Style

78. Activate A1 Reference Style

79. Insert Time Range

80. Convert Date into Day

81. Convert Date into Year

82. Remove Time from Date

83. Remove Date from Date and Time

84. Convert to Upper Case

85. Convert to Lower Case

86. Convert to Proper Case

87. Convert to Sentence Case

88. Remove a Character from Selection

89. Word Count from Entire Worksheet

90. Remove the Apostrophe from a Number

91. Remove Decimals from Numbers

92. Multiply all the Values by a Number

93. Add a Number in all the Numbers

94. Calculate the Square Root

95. Calculate the Cube Root

96. Add A-Z Alphabets in a Range

97. Convert Roman Numbers into Arabic Numbers

98. Remove Negative Signs

99. Replace Blank Cells with Zeros

100?

Well, I want you to share your favorite macro code which you use everyday to save your time.

Please share with me in the comment section, I’d love to hear from you.

And in the end, I just want to say that some these codes every day to increase my productivity and I’m sure it will also help you in your work.

I hope you have found this list useful. If yes, then please don’t forget to share this list with your friends.

Download Free PDF Copy

Enter your name and email below to get a Free PDF copy directly into your inbox.

2018-08-05T12:24:18+00:00
  • manish Chaurasiya says:

    Thank you for sharing code in easier way, this is very helpful. I am just beginner in macro. I have written a code to connect sql database but i m getting difficulty to connect more than one database from different server.

  • Sally says:

    Great list! Thank you!

  • loran shahin says:

    thank Puneet Gogia

  • Guru says:

    Thanks Puneet, this is so helpful!

  • Gideon says:

    Please I want the VBA code to merge multiple excel sheet in one. Can you send me the code please.

  • Steven Brown says:

    I’m getting a type mismatch when I run the ‘HighlightAlternateRows’ procedure. Debug shows this line as the culprit:
    ‘rng.Value = rng ^ (1 / 3)’
    Any help would be appreciated. Thanks

    • Steven Brown says:

      I fixed it and it works!

      Sub Highlight AlternateRows ()
      Dim rng As Range
      __For Each rng In Selection.Rows
      ____If rng.Row Mod 2 = 1 Then
      ____rng.Style = “20% – Accent1”
      ____Else
      ____End If
      __Next rng
      End Sub

      (underscores added to show proper indenting)

  • Carlos Mario Castaño says:

    1. In Insert Multiple Rows please change the word “columns” by “rows” in
    i = InputBox(“Enter number of columns to insert”, “Insert Columns”)

    2. Closing Message
    “You can use close_open to perform a task on opening a file. All you have to do just name your macro
    “close_open”.”
    Sub auto_close()
    The name of the macro is not “close_open”
    3. Count/Highlight Cells With Error In Entire Worksheet
    These statements must be in different lines:
    i = i + 1 rng.Style = “bad”
    4. In Count/Highlight Cells With A Specific In Entire Worksheet please add the word “Value” after “Specific”

  • Carlos Mario Castaño says:

    Although it worked for me, in the first Basic Macro, I have two observations:

    1) I think that the For loop must be something like:
    Dim j as integer
    For j = 1 to i
    ActiveCell.Value = j
    ActiveCell.Offset(1, 0).Activate
    Next j
    that is, replace “i” by j in the index variable for the loop
    2) As I said, your original code works but I think that the index variable must be different to the inputbox variable.
    Sincerely yours,
    Carlos

  • Addison O'Conner says:

    Hello! I would like a very specific code that I haven’t been able to find anywhere on the internet…
    I’m wanting the macro to identify blank cells in Row 1 only, delete the blank cells (in Row 1) & shift those columns’ cells up. Any advice??

    • Chris says:

      Try this:
      ‘ DeleteBlankCellsinRow1 Macro
      Sub DeleteBlankCellsinRow1()
      Rows(“1:1”).Select
      Selection.SpecialCells(xlCellTypeBlanks).Select
      Selection.Delete Shift:=xlUp
      End Sub

  • Ron007 says:

    Great list, but it would be nice if you made it easier for us to identify changes in it since you say you make monthly changes.

    Rather than a PDF, how about putting this tips in a downloadable spreadsheet or Word document that the tips can be sorted by insertion date as well as category. Actually. If you make the download cumulative that would be even better. So each month we could replace the old file with the new one, containing the all of the tips you’ve published here over time. Even if you start now building the cumulative file, that would be better.

    • Puneet Gogia says:

      Thanks for the great advice.

  • S raw says:

    Hello Sir,

    I want to learn how to create macro. I have the basic knowledge of VB. Please advise from where should I start.

  • Pravin Bhaiswar says:

    Sir, It’s extremely nice efforts. Would you save your precious time to modify one code of you you did? I need it should ask us location to save ask us to rename the file name before save………in the code for “Save Selected Range as a PDF” please modify this…..it will be beneficial for all…….please send me to bhaiswarpravin@gmail.com

    • Puneet Gogia says:

      Thanks you Pravin, for the correction. Will correct it soon,

      • Pravin Bhaiswar says:

        Sir, I used this code at my office…it was nice working but on my home pc..it says “Run time error 5: Invalid procedure call or argument…….So please have solution sir.

        • Puneet Gogia says:

          Which version of Microsoft Office you are using?

          • Pravin Bhaiswar says:

            Office 2007

  • Niharika Mehra says:

    Hi.. Can someone help me with the vba code to apply filter in pivot table.

  • Khaja Raziuddin says:

    Hello, really nice to see all these. is there any macro to copy data from different file to master file. if yes then can anyone please share

    • Puneet Gogia says:

      You can use power query from that.

  • Colleen Armstrong says:

    Thank you for these!!! I am going to start putting many of the to use tomorrow!!

  • Himanshu Tiwari says:

    Nice Work.

    I was also trying to get in touch with you to understand if there is a way we can filter a table based on unique values

    Let’s say we have the following table on the meetings done by a person, and the start time of each meeting

    Date Start Time
    ———- ————–
    Aug 24 9:00 AM
    Aug 24 10:00 AM
    Aug 24 1:45 PM
    Aug 24 4:45 PM
    Aug 25 8:00 AM
    Aug 25 2:00 PM
    Aug 25 5:00 PM
    Aug 26 12:30 PM
    Aug 26 2:00 PM
    Aug 26 4:29 PM
    Aug 26 8:28 PM

    Now I need to calculate the average start time for the period (say week / month) from such a table.
    How to do so?

    The best way I have right now is copy both the columns, and check for duplicates in the Date column, followed by which I get to calculate the average start time. Something like this.
    Date Start Time
    ——— ——————-
    Aug 24 9:00 AM
    Aug 25 8:00 AM
    Aug 26 12:30 PM

    Can we build a formula to decrease the above task.

  • Dilshad Ahmad says:

    Awesome! Puneet !

    • Puneet Gogia says:

      Thanks for your words.

  • Dhananjay Jadhav says:

    Great Puneet! Many of these are new & innovative for me. I am sure it will help me save my hours of daily work. Thanks much

    • Puneet Gogia says:

      I’m so glad you liked it.

  • Hema Deepak Raturi says:

    How to transfer a cell value from main workbook to several workbooks via VBA program, without opening the other several workbooks.

  • Jeff Faul says:

    Hey, great macros. Question, I’m using macro 31 “add rows textbox” I’m adding rows at line 35 and then I need to copy the formula from h34 down to all the new rows. Could you help with this addition?

    • Puneet Gogia says:

      I assume you want to insert a new row and copy formula as well?

      • Jeff Faul says:

        Yes, whatever number of rows are added I need the formula copied to every new row

      • Jeff Faul says:

        Here is what I currently have:

        Sub InsertMultipleRows()
        Dim i As Integer
        Dim j As Integer
        Rows(“35:35”).Select
        On Error GoTo Last
        i = InputBox(“Enter number of items to add”, “Insert Items”)
        For j = 1 To i
        Selection.Insert Shift:=xlToDown, CopyOrigin:=xlFormatFromRightorAbove
        Next j
        Last:
        Range(“H34”).Select
        Selection.AutoFill Destination:=Range(“H34:H35”), Type:=xlFillDefault
        ‘Range(“H34″+i).Select (This is the part I need help with)
        Range(“C35”).Select
        Exit Sub
        End Sub

        • Jeff Faul says:

          Nevermind, I figured it out

          • Puneet Gogia says:

            I’m sorry I just missed your update. Please share with me here so that other can make use of it.

  • Kushal R Jaju says:

    Hi Puneet.. It’s a great website and I’m learning something every day. Thanks for that..

    Save as PDF Command not working, can you please help. ?

    • Puneet Gogia says:

      Please share the error you got.

  • Venkateshwara Iyer says:

    i like to have the code to convert text to uppercase for entire sheet

  • Venkateshwara Iyer says:

    SUPERB

  • Vipul says:

    Great
    Really helpful
    Would like to see more VBA codes.

    Thanks

    • Puneet Gogia says:

      updated new codes

  • vishesh says:

    Punit i am very novice to VB, so can you give some tips how to grow up in VB coding, your kind guidance is required, my email id is gshuvishesh@gmail.com

  • vishesh says:

    Thanks Punit for sharing Wonderful Excel tricks….Helped me to automate my few daily routine task in one go….

    • Puneet Gogia says:

      I’m so glad you liked it.

  • gabriel gajardo says:

    hi!
    i work a lot with vba, a have some codes that can be useful, lake a parametric sendMail or send a worksheet or range as body mail. if you wan to add the just get in touch.

    by the way, thanks for share some codes.

    • Puneet Gogia says:

      Thank you, you can share with me.

  • ratanak says:

    Really nice,i’d love it.thanks puneet

    • Puneet Gogia says:

      Thanks Ratanak, For Your Words

  • Inet Kemp says:

    nice…highlight active row and column

  • >