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.

And 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.

But here's the first thing to know:

What is a Macro Code?

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

The idea behind using a macro code is to automate an action 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

Once you activate developer tab, you can use below steps to paste a VBA code into VB editor.

  • 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 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.
    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 from where  you can run a macro from that list.
    useful-macro-codes-examples-list-from-macro-option

...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.

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

Formatting Codes

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

1. Highlight Duplicates from Selection

2. Highlight the Active Row and Column

3. Highlight Top 10 Values

4. Highlight Named Ranges

5. Highlight Greater than Values

6. Highlight Lower Than Values

7. Highlight Negative Numbers

8. Highlight Specific Text

9. Highlight Cells with Comments

10. Highlight Alternate Rows in the Selection

11. Highlight Cells with Misspelled Words

12. Highlight Cells With Error in the Entire Worksheet

13. Highlight Cells with a Specific Text in Worksheet

14. Highlight all the Blank Cells Invisible Space

15. Highlight Max Value In The Range

16. Highlight Min Value In The Range

17. Highlight Unique Values

18. Highlight Difference in Columns

19. Highlight Difference in Rows

Printing Codes

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

1. Print Comments

2. Print Narrow Margin

3. Print Selection

4. Print Custom Pages

Worksheet Codes

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

1. Hide all but the Active Worksheet

2. Unhide all Hidden Worksheets

3. Delete all but the Active Worksheet

4. Protect all Worksheets Instantly

5. Resize All Charts in a Worksheet

6. Insert Multiple Worksheets

7. Protect Worksheet

8. Un-Protect Worksheet

9. Sort Worksheets

10. Protect all the Cells With Formulas

11. Delete all Blank Worksheets

12. Unhide all Rows and Columns

13. Save Each Worksheet as a Single PDF

14. Disable Page Breaks

Workbook Codes

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

1. Create a Backup of a Current Workbook

2. Close all Workbooks at Once

3. Copy Active Worksheet into a New Workbook

4. Active Workbook in an Email

5. Add Workbook to a Mail Attachment

6. Welcome Message

7. Closing Message

8. Count Open Unsaved Workbooks

Pivot Table Codes

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

1. Hide Pivot Table Subtotals

2. Refresh All Pivot Tables

3. Create a Pivot Table

4. Auto Update Pivot Table Range

5. Disable/Enable Get Pivot Data

Charts Codes

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

1. Change Chart Type

2. Paste Chart as an Image

3. Add Chart Title

Advanced Codes

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

1. Save Selected Range as a PDF

2. Create a Table of Content

3. Convert Range into an Image

4. Insert a Linked Picture

5. Use Text to Speech

6. Activate Data Entry Form

7. Use Goal Seek

8. VBA Code to Search on Google

Formula Codes

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

1. Convert all Formulas into Values

2. Remove Spaces from Selected Cells

3. Remove Characters from a String

4. Add Insert Degree Symbol in Excel

5. Reverse Text

6. Activate R1C1 Reference Style

7. Activate A1 Reference Style

8. Insert Time Range

9. Convert Date into Day

10. Convert Date into Year

11. Remove Time from Date

12. Remove Date from Date and Time

13. Convert to Upper Case

14. Convert to Lower Case

15. Convert to Proper Case

16. Convert to Sentence Case

17. Remove a Character from Selection

18. Word Count from Entire Worksheet

19. Remove the Apostrophe from a Number

20. Remove Decimals from Numbers

21. Multiply all the Values by a Number

22. Add a Number in all the Numbers

23. Calculate the Square Root

24. Calculate the Cube Root

25. Add A-Z Alphabets in a Range

26. Convert Roman Numbers into Arabic Numbers

27. Remove Negative Signs

28. Replace Blank Cells with Zeros

In the End,

It’s your turn now.

Yes.

I want you to share your favorite macro code with me…

…which you use every day to save your time.

In the end, I just want to say that some of these codes I use every day to increase my productivity…

…and I’m sure it will also help you in your work.

106 thoughts

Leave a Comment

Your email address will not be published.

  1. Hello,

    I have a macro which will consolidate all workbooks to single sheet but i need to have files names as well in each row to indentify how many lines from workbook

  2. I particularly like this code for Superscripting when I want to show X squared for example. It can be modified to subscript as well and to return back to regular text.
    When writing out problems with formulas for students this can be easier than using the format/ cell with the mouse.

    ActiveCell.FormulaR1C1 = “X2”
    With ActiveCell.Characters(Start:=1, Length:=1).Font
    .Name = “Calibri (Theme Body)”
    .FontStyle = “Regular”
    .Size = 12
    .StrikeThrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    End With
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = “Calibri (Theme Body)”
    .FontStyle = “Regular”
    .Size = 12
    .StrikeThrough = False
    .Superscript = False
    .Subscript = True
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    End With
    End Sub

  3. Thank You Very Much.Its all very useful. I suggest one thing please comment how to use the each codes some of the codes can run only by coder.

  4. Hai,

    I need the numbers 1-100 or 1-1000 in a jumbling manner. is there any code, i need it very urgently. pls. can u help me in this.

  5. Hey Buddy,

    thanks a ton. your macros are of great help.

    can you create a macro wherein i can remove formulas from cells where cell value is not in percentage.

  6. Hi,

    I dont know macro well.

    I want a code where I just put data in sheet1 and the pivot charts automatic created. Can anyone please help me on this.

    It is very urgent.

  7. In this below code how I will define range. I just put data in sheet1 I dont know the data size like how much column and row are present in the data. So I want to put some dynamic range so that any data can useful.
    Please help ASAP.

    Sub Macro2()

    ‘ Macro2 Macro


    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    “Sheet1!R1C1:R6C73″, Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:=”Sheet2!R3C1″, TableName:=”PivotTable1”, DefaultVersion _
    :=xlPivotTableVersion15
    Sheets(“Sheet2”).Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Created Date”)
    .Orientation = xlRowField
    .Position = 1
    End With
    ActiveSheet.PivotTables(“PivotTable1”).AddDataField ActiveSheet.PivotTables( _
    “PivotTable1”).PivotFields(“Incident Id”), “Count of Incident Id”, xlCount
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range(“Sheet2!$A$3:$B$8”)
    End Sub

  8. Dear Puneet,

    This site is amazing and i get to learn something new every passing day. Sincere thanks for your time and initiative.

    I am trying to create a date stamp button (using form control) that will add customized date and time of printing in the excel footer – using a specific font, font size and font color (e.g. Veranda, 8pt, Blue)

    The end result would look something like this:
    Printed on dd-mmm-yyyy at hh:mm:ss

    I don’t want the time stamp to be inserted automatically, but rather use a form control button to insert the same when clicked.

    Could you kindly help me with the VBA code please? It will be a great help!

    TIA for you help & warm regards
    Ranjitha

  9. Hi Puneet

    I am looking for a stock report with a huge data my requirement is
    Material dispatch planning (Main Moto – FIFO Basis)
    Outstanding Orders

  10. Hi Punnet

    First of all ” Thanks a lot for the Great Work ”

    I am looking for a code that will consolidate data from multiple excel files in a specific folder to a new blank excel file.

    • I hope below 2 VBA Code will help you in your question…

      1. Combine Multiple Workbooks into One Workbook:

      Sub GetData()
      Dim sh As Worksheet
      Path = “D:\(Give Path Name where all excel files are saved)\”
      Filename = Dir(Path & “*.xlsx”)
      Do While Filename “”
      Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
      For Each sh In ActiveWorkbook.Worksheets
      ‘If LCase(Left(sh.Name, 5)) = “model” Then
      sh.Copy After:=ThisWorkbook.Sheets(1)
      ‘End If
      Next sh
      Workbooks(Filename).Close
      Filename = Dir()
      Loop
      End Sub

      2. To Combine Multiple Worksheets into One WorkSheet.:

      Sub Combine()
      Dim J As Integer
      On Error Resume Next
      Sheets(1).Select
      Worksheets.Add
      Sheets(1).Name = “Data”
      Sheets(2).Activate
      Range(“A1”).EntireRow.Select
      Selection.Copy Destination:=Sheets(1).Range(“A1”)
      For J = 2 To Sheets.Count
      Sheets(J).Activate
      Range(“A1”).Select
      Selection.CurrentRegion.Select
      Selection.Offset(1, 0).Resize(Selection.Rows.Count – 1).Select
      Selection.Copy Destination:=Sheets(1).Range(“A65536”).End(xlUp)(2)
      Next
      End Sub

      • Hi sir,
        i need your help.
        I will download one file 10000 lines coming one excel sheet.
        every line mentions the Projects No.
        Project no wise split the data to convert the workbook
        if it is possible to share the coding

  11. Hello,

    I would like to know the VBA code to copy an active sheet to multiple sheets in the same work book.

    Thank you

  12. ExcelChamps, Good evening. I’m new to VBA. Excuse me if my question too silly. I have a column in which there will be names of cities. If I type/select that name from drop down list, excel should populate pin code number in the next column. Also some other columns to be autofilled. For example, point of contact name and number of that city. My EmailID is sree21343@gmail.com. Thanks in advance

  13. Hi I am Looking for a way to convert a Rage Named which is and auto Start Name xls in XP Excel 97-2003 to vba code in Excel 2010 Windows 10 Is there a Way? or do I just need to start over?

  14. I could not get the code for highlighting the row and column of the cell I’m working on to function:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim strRange As String
    strRange = Target.Cells.Address & “,” & _
    Target.Cells.EntireColumn.Address & “,” & _
    Target.Cells.EntireRow.Address
    Range(strRange).Select
    End Sub

    Any suggestions?

    • It worked for me when changing the quotation marks from “” to “”
      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      Dim strRange As String
      strRange = Target.Cells.Address & “,” & _
      Target.Cells.EntireColumn.Address & “,” & _
      Target.Cells.EntireRow.Address
      Range(strRange).Select
      End Sub

  15. Here are some of the codes I use on a daily basis.

    Function ConvertColumnNumberToLetter(colNum)
    ‘Getting the address of the first row and the colNum column number
    colAdr = ActiveWorkbook.ActiveSheet.Cells(1, colNum).Address

    With Application.WorksheetFunction
    colLetter = .Find(“$”, colAdr, 2) ‘Finding the second $-sign in the address
    ConvertColumnNumberToLetter = Mid(colAdr, 2, colLetter – 2) ‘Extracting the middle part of the address, containing only the letter(s) and returning it/them
    End With
    End Function

    Function ConvertColumnLetterToNumber(colLet As String)
    With ActiveWorkbook.ActiveSheet
    colAdr = .range(colLet & 1).Address ‘Getting the address of the first row and the colNum column number
    ConvertColumnLetterToNumber = .range(colAdr).Column ‘Getting the column number of the address
    End With
    End Function

    Function SendSelectionAsEmail(rng As range, subj As String, sendTo As String, Optional ccTo As String, Optional intro As String)

    ‘ Select the range of cells on the active worksheet.
    ActiveSheet.range(rng).Select

    ‘ Show the envelope on the ActiveWorkbook.
    ActiveWorkbook.EnvelopeVisible = True

    ‘ Set the optional introduction field thats adds
    ‘ some header text to the email body. It also sets
    ‘ the To and subject lines. Finally the message
    ‘ is sent.
    With ActiveSheet.MailEnvelope
    .Introduction = intro
    .Item.To = sendTo
    .Item.CC = ccTo
    .Item.Subject = subj
    .Item.Send
    End With
    End Function

    Function ConvertCollectionToArray(col As Collection)
    Dim arr() As Variant
    ReDim arr(1 To col.Count) As Variant
    For i = 1 To col.Count
    arr(i) = col(i)
    Next i
    toArray = arr
    End Function

    Function LastRow(ws As Worksheet, columnNumberToCheck)
    LastRow = ws.Cells(Rows.Count, columnNumberToCheck).End(xlUp).Row
    End Function

    Function LastColumn(ws As Worksheet, rowNumberToCheck)
    LastColumn = ws.Cells(rowNumberToCheck, Columns.Count).End(xlToLeft).Column
    End Function

    Function GetLastRowAdvaned(ws As Worksheet, endColumnNumber) ‘Looping through all columns from 1 to the end column number and finding the max value
    maxVal = 0
    For i = 1 To endColumnNumber
    If LastRow(ws, i) > maxVal Then
    maxVal = LastRow(ws, i)
    End If
    Next i
    GetLastRowAdvaned = maxVal
    End Function

    Function IsRowEmpty(ws As Worksheet, rowNumberToCheck, endColumnNumber) As Boolean
    Dim isEmpty As Boolean
    isEmpty = True

    For i = 1 To endColumnNumber
    If ws.range(Cells(rowNumberToCheck, i).Address) = “” Then
    IsRowEmpty = True
    Else
    IsRowEmpty = False
    GoTo EndFunction:
    End If
    Next i

    EndFunction:
    IsRowEmtpy = isEmpty
    End Function

  16. This macro will promt you to select a photo, then it will size the height ,width and insert it to a specific range.

    Sub Insert_Setup_Photo()

    ActiveSheet.Protect DrawingObjects:=False

    Dim picToOpen As String
    picToOpen = Application.GetOpenFilename _
    (Title:=”Select Setup Photo To Insert”)

    If picToOpen = “False” Then
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True
    Exit Sub
    End If

    Dim shp As Shape, t As Double, l As Double, w As Double, h As Double, r As Integer
    Dim Cel As Range

    CellHeight = 375 ‘Final Image Height, maintains scale
    CellWidth = 670 ‘Final Image Width, maintains scale

    Set Cel = Range(“B5:M29”) ‘Cells image be centered

    With Cel

    Set shp = ActiveSheet.Shapes.AddPicture(Filename:=picToOpen, _
    LinkToFile:=msoFalse, SaveWithDocument:=msoCTrue, Left:=0, Top:=0, Width:=-1, Height:=-1)
    shp.Locked = False
    shp.Height = CellHeight

    If shp.Width > CellWidth Then
    shp.Width = CellWidth
    End If

    shp.Left = .Left + ((.Width – shp.Width) / 2)
    shp.Top = .Top + ((.Height – shp.Height) / 2)

    End With

    ActiveSheet.Protect DrawingObjects:=False, Contents:=True

    End Sub

  17. I Would like move the cursor from active cell to one cell left,right(From selected cell)..same thing to Up and Down by writing macros, Could you please help out resolve the task. I tried to write the below but its not working fine. Please let me know.
    Sub Left(). Ex: Hear Activecell is C2
    activecell.offset(0,-1).select -> hear it moves B2 cell
    end sub
    sub right()
    activecell.offset(0,1) .select -> hear it moves C2 But I want to move to D2. Same thing to Up and Down
    end sub

    Thanks,
    Vivek.

  18. hi, I want to create Outlook email macro where emails automatically gets saved in to shared drive. Help needed with coding if anyone knows.

  19. hi punnet sir

    thank you for providing macro code. These codes provide me the advantage of time in my work. these coding also provide my coding awareness . after having understood from these 100 codes. I have started creating small macro coding.

    thank you very much.

  20. Hi Puneet,

    Great job.

    Shall i get code for deleting rows if any of the column contains blank cells

    Thanks

  21. I need VBR code to work across all worksheets in a workbook a future date will be manually entered in the same cell on every sheet and new sheets are created daily. On the actual day of the date entered an email notification would be sent out I also need the worksheet name in the email so I will know which sheet is due.

  22. Dear, I am using following code for transferring data from one sheet to another sheet, three variable parameters, i.e. between two dates and center which are selected from Dropdown menu from Main sheet.
    Programme run successfully, but each record written, i should press cancel button, after last record transfer, all data changed. I think, there is formula on CRM(Data) sheet and while transferring data formula also transferred, so data will be changed after running.

    Pl guide me in the matter.

    Sub Module()
    ‘SelectDataBetweenTwoDates()

    ‘declare variables
    Dim fromDate, toDate
    Dim MyResults As Worksheet, myData As Worksheet, MyDates As Worksheet
    Dim mModule As String

    Set MyResults = Worksheets(“MODCRM”)
    Set myData = Worksheets(“CRM”)
    Set MyDates = Worksheets(“Main”)

    ‘clear previous results
    MyResults.Range(“$A$3:$K$450”).ClearContents

    ‘attribute date values to variables
    fromDate = MyDates.Range(“D7”).Value
    toDate = MyDates.Range(“D9”).Value
    mModule = MyDates.Range(“D5”).Value

    ‘convert to text format to allow filtering
    fromDate = Format(fromDate, “dd-mmm-yyyy”)
    toDate = Format(toDate, “dd-mmm-yyyy”)

    With myData
    ‘removes autofilter
    If .FilterMode Then .ShowAllData
    ‘filter the data based on selected date values
    .Range(“$A$2:$K$2”).AutoFilter field:=7, Criteria1:= _
    “>=” & fromDate, Operator:=xlAnd, Criteria2:=”<=" & toDate
    .Range("$A$2:$K$2").AutoFilter field:=4, Criteria1:=mModule

    'copy the filtered data
    .UsedRange.SpecialCells(xlCellTypeVisible).Copy

    'paste copied values to results sheet
    MyResults.Range("A1").PasteSpecial

    End With

    ' remove autofilter in mydata

    'select cell A1 in results sheet
    MyResults.Activate
    MyResults.Range("A1").Select

    End Sub

    Please any one can guide me in the matter.
    while copying (xlCellTypeVisible) data copied with formula instead of values only.

    Pl guide me in the matter.

    Ravi Patel

  23. Hi,

    I am using macro for auto filter on multiple filter with between dates and center, every thing is run successfully, but i have to press cancel button at every record then record display on screen, after last record, all record changed this i due to formula on sheet,

    Any one help me.

  24. Hi,

    I have Stock and requiremets and required Output as given below.
    Stock
    Mat Code Mat Description Batch Avlb STK
    DS1977S40014C01 CO/SAT/400TC/114/IVORY C16184204 100
    DS1977S40014C01 CO/SAT/400TC/114/IVORY C16184206 100
    DS1977S40014C01 CO/SAT/400TC/114/IVORY G15833208 100
    DS1977S40014C01 CO/SAT/400TC/114/IVORY C16150304 750
    DS1977S40014C01 CO/SAT/400TC/114/IVORY C16151502 250
    DS1977S40014C01 CO/SAT/400TC/114/IVORY C16151501 250
    DS1977S40014C01 CO/SAT/400TC/114/IVORY C16150305 600

    Requirements
    SR NO Mat Code Mat Description SO Req Qty
    3161313530 DS1977S40014C01 CO/SAT/400TC/114/IVORY 300.000
    3161313573 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200.000
    3161313574 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200.000
    3161313575 DS1977S40014C01 CO/SAT/400TC/114/IVORY 350.000
    3161313576 DS1977S40014C01 CO/SAT/400TC/114/IVORY 500.000
    3161313538 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200.000
    3161313539 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200.000
    3161313540 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200.000

    Required OutPut:-
    SR No Mat Code Mat Description Req. Qty Batch Available qty Consumed remarks
    3161313530 DS1977S40014C01 CO/SAT/400TC/114/IVORY 100 C16184204 100 100 SO qty Spilt
    3161313530 DS1977S40014C01 CO/SAT/400TC/114/IVORY 100 C16184206 100 100 SO qty Spilt
    3161313530 DS1977S40014C01 CO/SAT/400TC/114/IVORY 100 G15833208 100 100 SO qty Spilt
    3161313573 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200 C16150304 750 200 Batch qty Spilt
    3161313574 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200 C16150304 550 200 Batch qty Spilt
    3161313575 DS1977S40014C01 CO/SAT/400TC/114/IVORY 350 C16150304 350 350 Batch qty Spilt
    3161313576 DS1977S40014C01 CO/SAT/400TC/114/IVORY 250 C16151502 250 250 SO qty Spilt
    3161313576 DS1977S40014C01 CO/SAT/400TC/114/IVORY 250 C16151501 250 250 SO qty Spilt
    3161313538 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200 C16150305 600 200 Batch qty Spilt
    3161313539 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200 C16150305 400 200 Batch qty Spilt
    3161313540 DS1977S40014C01 CO/SAT/400TC/114/IVORY 200 C16150305 200 200 Batch qty Spilt

    Pls help on this..

  25. Sub SaveAs()

    ‘ SaveAs Macro

    ‘ Keyboard Shortcut: Ctrl+Shift+A

    ChDir “D:”
    ActiveWorkbook.SaveAs Filename:= _
    “D:gst Billing System2018.xlsm”, FileFormat:= _
    xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ChDir “E:JSM”
    ActiveWorkbook.SaveAs Filename:=”E:JSMBilling System2018.xlsm”, FileFormat:= _
    xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End Sub

  26. Thanks Puneet, this is so helpful

    Need your help
    Can you help create a macro for this instance in excel:

    N/A
    Abc
    123
    (Blank Cell)
    XYZ
    N/A
    123-222
    N/A
    (Blank Cell)

    Answer: Abc, 123, XYZ and 123-222

    Thanks in advance

  27. Hey Hi,
    Thanks for the codes.
    I need your help to count the excel cell colors which used by conditional formatting. I had tried many ways, but, no luck.

    Kindly let me know, if you can help.

  28. 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.

  29. 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

    • 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)

  30. 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”

  31. 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

  32. 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??

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

  33. 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.

  34. Hello Sir,

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

  35. 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