One way of getting the VBA code is to record the macro and take the code it generates. However, that code by macro recorder is often full of code that is not really needed. Also macro recorder has some limitations. So it pays to have a collection of useful VBA macro codes that you can have in your back pocket and use it when needed. While writing an Excel VBA macro code may take some time initially, once it’s done, you can keep it available as a reference and use it whenever you need it next. In this massive article, I am going to list some useful Excel macro examples that I need often and keep stashed away in my private vault. I will keep updating this tutorial with more macro examples. If you think something should be on the list, just leave a comment. You can bookmark this page for future reference. Now before I get into the Macro Example and give you the VBA code, let me first show you how to use these example codes.
Using the Code from Excel Macro Examples
Here are the steps you need to follow to use the code from any of the examples:
Open the Workbook in which you want to use the macro. Hold the ALT key and press F11. This opens the VB Editor. Right-click on any of the objects in the project explorer. Go to Insert –> Module. Copy and Paste the code in the Module Code Window.
In case the example says that you need to paste the code in the worksheet code window, double click on the worksheet object and copy paste the code in the code window. Once you have inserted the code in a workbook, you need to save it with a .XLSM or .XLS extension.
How to Run the Macro
Once you have copied the code in the VB Editor, here are the steps to run the macro:
Go to the Developer tab. Click on Macros.
In the Macro dialog box, select the macro you want to run. Click on Run button.
In case you can’t find the developer tab in the ribbon, read this tutorial to learn how to get it. Related Tutorial: Different ways to run a macro in Excel.
In case the code is pasted in the worksheet code window, you don’t need to worry about running the code. It will automatically run when the specified action occurs. Now, let’s get into the useful macro examples that can help you automate work and save time. Note: You will find many instances of an apostrophe (‘) followed by a line or two. These are comments that are ignored while running the code and are placed as notes for self/reader. In case you find any error in the article or the code, please be awesome and let me know.
Excel Macro Examples
Below macro examples are covered in this article:
Unhide All Worksheets at One Go
If you are working in a workbook that has multiple hidden sheets, you need to unhide these sheets one by one. This could take some time in case there are many hidden sheets. Here is the code that will unhide all the worksheets in the workbook. The above code uses a VBA loop (For Each) to go through each worksheets in the workbook. It then changes the visible property of the worksheet to visible. Here is a detailed tutorial on how to use various methods to unhide sheets in Excel.
Hide All Worksheets Except the Active Sheet
If you’re working on a report or dashboard and you want to hide all the worksheet except the one that has the report/dashboard, you can use this macro code.
Sort Worksheets Alphabetically Using VBA
If you have a workbook with many worksheets and you want to sort these alphabetically, this macro code can come in really handy. This could be the case if you have sheet names as years or employee names or product names.
Protect All Worksheets At One Go
If you have a lot of worksheets in a workbook and you want to protect all the sheets, you can use this macro code. It allows you to specify the password within the code. You will need this password to unprotect the worksheet.
Unprotect All Worksheets At One Go
If you have some or all of the worksheets protected, you can just use a slight modification of the code used to protect sheets to unprotect it. Note that the password needs to the same that has been used to lock the worksheets. If it’s not, you will see an error.
Unhide All Rows and Columns
This macro code will unhide all the hidden rows and columns. This could be really helpful if you get a file from someone else and want to be sure there are no hidden rows/columns.
Unmerge All Merged Cells
It’s a common practice to merge cells to make it one. While it does the work, when cells are merged you will not be able to sort the data. In case you are working with a worksheet with merged cells, use the code below to unmerge all the merged cells at one go. Note that instead of Merge and Center, I recommend using the Centre Across Selection option.
Save Workbook With TimeStamp in Its Name
A lot of time, you may need to create versions of your work. These are quite helpful in long projects where you work with a file over time. A good practice is to save the file with timestamps. Using timestamps will allow you to go back to a certain file to see what changes were made or what data was used. Here is the code that will automatically save the workbook in the specified folder and add a timestamp whenever it’s saved. You need to specify the folder location and the file name. In the above code, “C:UsersUsernameDesktop is the folder location I have used. You need to specify the folder location where you want to save the file. Also, I have used a generic name “WorkbookName” as the filename prefix. You can specify something related to your project or company.
Save Each Worksheet as a Separate PDF
If you work with data for different years or divisions or products, you may have the need to save different worksheets as PDF files. While it could be a time-consuming process if done manually, VBA can really speed it up. Here is a VBA code that will save each worksheet as a separate PDF. In the above code, I have specified the address of the folder location in which I want to save the PDFs. Also, each PDF will get the same name as that of the worksheet. You will have to modify this folder location (unless your name is also Sumit and you’re saving it in a test folder on the desktop). Note that this code works for worksheets only (and not chart sheets).
Save Each Worksheet as a Separate PDF
Here is the code that will save your entire workbook as a PDF in the specified folder. You will have to change the folder location to use this code.
Convert All Formulas into Values
Use this code when you have a worksheet that contains a lot of formulas and you want to convert these formulas to values. This code automatically identifies cells are used and convert it into values.
Protect/Lock Cells with Formulas
You may want to lock cells with formulas when you have a lot of calculations and you don’t want to accidentally delete it or change it. Here is the code that will lock all the cells that have formulas, while all the other cells are not locked. Related Tutorial: How to Lock Cells in Excel.
Protect All Worksheets in the Workbook
Use the below code to protect all the worksheets in a workbook at one go. This code will go through all the worksheets one by one and protect it. In case you want to unprotect all the worksheets, use ws.Unprotect instead of ws.Protect in the code.
Insert A Row After Every Other Row in the Selection
Use this code when you want to insert a blank row after every row in the selected range. Similarly, you can modify this code to insert a blank column after every column in the selected range.
Automatically Insert Date & Timestamp in the Adjacent Cell
A timestamp is something you use when you want to track activities. For example, you may want to track activities such as when was a particular expense incurred, what time did the sale invoice was created, when was the data entry done in a cell, when was the report last updated, etc. Use this code to insert a date and time stamp in the adjacent cell when an entry is made or the existing contents are edited. Note that you need to insert this code in the worksheet code window (and not the in module code window as we have done in other Excel macro examples so far). To do this, in the VB Editor, double click on the sheet name on which you want this functionality. Then copy and paste this code in that sheet’s code window. Also, this code is made to work when the data entry is done in Column A (note that the code has the line Target.Column = 1). You can change this accordingly.
Highlight Alternate Rows in the Selection
Highlighting alternate rows can increase the readability of your data tremendously. This can be useful when you need to take a print out and go through the data. Here is a code that will instantly highlight alternate rows in the selection. Note that I have specified the color as vbCyan in the code. You can specify other colors as well (such as vbRed, vbGreen, vbBlue).
Highlight Cells with Misspelled Words
Excel doesn’t have a spell check as it has in Word or PowerPoint. While you can run the spell check by hitting the F7 key, there is no visual cue when there is a spelling mistake. Use this code to instantly highlight all the cells that have a spelling mistake in it. Note that the cells that are highlighted are those that have text that Excel considers as a spelling error. In many cases, it would also highlight names or brand terms that it doesn’t understand.
Refresh All Pivot Tables in the Workbook
If you have more than one Pivot Table in the workbook, you can use this code to refresh all these Pivot tables at once. You can read more about refreshing Pivot Tables here.
Change the Letter Case of Selected Cells to Upper Case
While Excel has the formulas to change the letter case of the text, it makes you do that in another set of cells. Use this code to instantly change the letter case of the text in the selected text. Note that in this case, I have used UCase to make the text case Upper. You can use LCase for lower case.
Highlight All Cells With Comments
Use the below code to highlight all the cells that have comments in it. In this case, I have used vbBlue to give a blue color to the cells. You can change this to other colors if you want.
Highlight Blank Cells With VBA
While you can highlight blank cell with conditional formatting or using the Go to Special dialog box, if you have to do it quite often, it’s better to use a macro. Once created, you can have this macro in the Quick Access Toolbar or save it in your personal macro workbook. Here is the VBA macro code: In this code, I have specified the blank cells to be highlighted in the red color. You can choose other colors such as blue, yellow, cyan, etc.
How to Sort Data by Single Column
You can use the below code to sort data by the specified column. Note that the I have created a named range with the name ‘DataRange’ and have used it instead of the cell references. Also there are three key parameters that are used here:
Key1 – This is the on which you want to sort the data set. In the above example code, the data will be sorted based on the values in column A. Order- Here you need to specify whether you want to sort the data in ascending or descending order. Header – Here you need to specify whether your data has headers or not.
Read more on how to sort data in Excel using VBA.
How to Sort Data by Multiple Columns
Suppose you have a dataset as shown below:
Below is the code that will sort the data based on multiple columns: Note that here I have specified to first sort based on column A and then based on column B. The output would be something as shown below:
How to Get Only the Numeric Part from a String in Excel
If you want to extract only the numeric part or only the text part from a string, you can create a custom function in VBA. You can then use this VBA function in the worksheet (just like regular Excel functions) and it will extract only the numeric or text part from the string. Something as shown below:
Below is the VBA code that will create a function to extract numeric part from a string: You need place in code in a module, and then you can use the function =GetNumeric in the worksheet. This function will take only one argument, which is the cell reference of the cell from which you want to get the numeric part. Similarly, below is the function that will get you only the text part from a string in Excel: So these are some of the useful Excel macro codes that you can use in your day-to-day work to automate tasks and be a lot more productive. Other Excel tutorials you may like:
How to Delete Macros in Excel How to Enable Macros in Excel?
i want to hyperlink my image with website url plz help me for hyperling my image! and i want to send it to outlook Sub Send_email_fromexcel() Dim edress As String Dim subj As String Dim message As String Dim filename, fname2 As String Dim outlookapp As Object Dim outlookmailitem As Object Dim myAttachments As Object Dim path As String Dim lastrow As Integer Dim attachment As String Dim x As Integer x = 2 Set outlookapp = CreateObject(“Outlook.Application”) Set outlookmailitem = outlookapp.createitem(0) Set myAttachments = outlookmailitem.Attachments path = “C:UsersUserDesktopstatements” edress = Sheet1.Cells(x, 1) subj = Sheet1.Cells(x, 2) filename = Sheet1.Cells(x, 3) fname2 = “Weddingplz-Safe-Gold.jpg” attachment = path + filename outlookmailitem.to = edress outlookmailitem.cc = “” outlookmailitem.bcc = “” outlookmailitem.Subject = subj outlookmailitem.Attachments.Add path & fname2, 1 outlookmailitem.htmlBody = “Thank you for your contract” _ & “nicely done this work” _ & “” outlookmailitem.htmlBody = “” & outlookmailitem.htmlBody & “” ‘outlookmailitem.body = “Please find your statement attached” & vbCrLf & “Best Regards” outlookmailitem.display ‘outlookmailitem.send lastrow = lastrow + 1 edress = “” x = x + 1 Set outlookapp = Nothing Set outlookmailitem = Nothing End Sub Please can you help me out with a suggestion? I’m not sure if VLOOKUPS or Macros or a combination of both are my best solution. What I do know is that a series of sums and manual entries won’t do the trick. Anyone able to help?? Eg. I have 3 sheet , I two sheet there are identical text and adjusted numeric value in another column. But in 3rd sheet the text name is little different so I can’t use vlookup here . So I want to for copy the text from 1st sheet cell and search that in 2nd sheet and go to its trace precedants and copy that cell and past at 1 sheet If Txtl = “” Or Txtb = “” Or Txtc = “” Or Txtr = “” Or Txtlpa = “” Or Txtd = “” Or Txtp = “” Or Txta = “” Or Txte = “” Or Txtw = “” Or Txtq = “” Or Txtdl = “” Or Txtehm = “” Or Txtco = “” Or Txtbo = “” Or Txtcurr = “” Or Txtwritten = “” Then MsgBox “PLEASE FILL ALL THE DETAILS” Txtl = “” Txtb = “” Txtc = “” Txtr = “” Txtlpa = “” Txtd = “” Txtp = “” Txta = “” Txte = “” Txtw = “” Txtq = “” Txtdl = “” Txtehm = “” Txtco = “” Txtbo = “” Txtcurr = “” Txtwritten = “” Cancle = True Exit Sub End If If Range(“A2”).Value = “” Then Sheets(“output”).Select Range(“A2”).Value = Txtl Range(“B2”).Value = Txtb Range(“C2”).Value = Txtc Range(“D2”).Value = Format(Txtr, “mm/dd/yyyy”) Range(“E2”).Value = Txtlpa If IsNumeric(Txtd) Then Range(“F2”).Value = Format(Txtd, “mm/dd/yyyy”) Else Range(“F2”).Value = Txtd End If If IsNumeric(Txtp) Then Range(“G2”).Value = Format(Txtp, “mm/dd/yyyy”) Else Range(“G2”).Value = Txtp End If If IsNumeric(Txta) Then Range(“H2”).Value = Format(Txta, “mm/dd/yyyy”) Else Range(“H2”).Value = Txta End If If IsNumeric(Txte) Then Range(“I2”).Value = Format(Txte, “mm/dd/yyyy”) Else Range(“I2”).Value = Txte End If If IsNumeric(Txtw) Then Range(“J2”).Value = Format(Txtw, “mm/dd/yyyy”) Else Range(“J2”).Value = Txtw End If If IsNumeric(Txtq) Then Range(“K2”).Value = Format(Txtq, “mm/dd/yyyy”) Else Range(“K2”).Value = Txtq End If If IsNumeric(Txtdl) Then Range(“L2”).Value = Format(Txtdl, “mm/dd/yyyy”) Else Range(“L2”).Value = Txtdl End If If IsNumeric(Txtehm) Then Range(“M2”).Value = Format(Txtehm, “mm/dd/yyyy”) Else Range(“M2”).Value = Txtehm End If Range(“N2”).Value = Txtco Range(“P2”).Value = Txtbo Range(“O2”).Value = Txtcurr Range(“Q2”).Value = Txtwritten Else: lr = Sheets(“output”).Range(“A” & Rows.Count).End(xlUp).Row Range(“A” & lr).Offset(1, 0).Value = Txtl Range(“B” & lr).Offset(1, 0).Value = Txtb Range(“C” & lr).Offset(1, 0).Value = Txtc Range(“D” & lr).Offset(1, 0).Value = Format(Txtr, “mm/dd/yyyy”) Range(“E” & lr).Offset(1, 0).Value = Txtlpa If IsNumeric(Txtd) Then Range(“F” & lr).Offset(1, 0).Value = Format(Txtd, “mm/dd/yyyy”) Else Range(“F” & lr).Offset(1, 0).Value = Txtd End If If IsNumeric(Txtp) Then Range(“G” & lr).Offset(1, 0).Value = Format(Txtp, “mm/dd/yyyy”) Else Range(“G” & lr).Offset(1, 0).Value = Txtp End If If IsNumeric(Txta) Then Range(“H” & lr).Offset(1, 0).Value = Format(Txta, “mm/dd/yyyy”) Else Range(“H” & lr).Offset(1, 0).Value = Txta End If If IsNumeric(Txte) Then Range(“I” & lr).Offset(1, 0).Value = Format(Txte, “mm/dd/yyyy”) Else Range(“I” & lr).Offset(1, 0).Value = Txte End If If IsNumeric(Txtw) Then Range(“J” & lr).Offset(1, 0).Value = Format(Txtw, “mm/dd/yyyy”) Else Range(“J” & lr).Offset(1, 0).Value = Txtw End If If IsNumeric(Txtq) Then Range(“K” & lr).Offset(1, 0).Value = Format(Txtq, “mm/dd/yyyy”) Else Range(“K” & lr).Offset(1, 0).Value = Txtq End If If IsNumeric(Txtdl) Then Range(“L” & lr).Offset(1, 0).Value = Format(Txtdl, “mm/dd/yyyy”) Else Range(“L” & lr).Offset(1, 0).Value = Txtdl End If If IsNumeric(Txtehm) Then Range(“M” & lr).Offset(1, 0).Value = Format(Txtehm, “mm/dd/yyyy”) Else Range(“M” & lr).Offset(1, 0).Value = Txtehm End If Range(“N” & lr).Offset(1, 0).Value = Txtco Range(“O” & lr).Offset(1, 0).Value = Txtbo Range(“P” & lr).Offset(1, 0).Value = Txtcurr Range(“Q” & lr).Offset(1, 0).Value = Txtwritten End If Txtl = “” Txtb = “” Txtc = “” Txtr = “” Txtlpa = “” Txtd = “” Txtp = “” Txta = “” Txte = “” Txtw = “” Txtq = “” Txtdl = “” Txtehm = “” Txtco = “” Txtbo = “” Txtcurr = “” Txtwritten = “” End Sub Private Sub Txtb_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) If IsNumeric(Txtn) > 50 Then MsgBox “pls fill text only” Txtn = “” Cancel = True End If End Sub Private Sub Txtb_Change() End Sub Private Sub Txtl_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) If Len(Txtl) > 8 Then Txtl = “” MsgBox “please 8 chr numric only ” Cancel = True End If If Not IsNumeric(Txtl) Then Txtl = “” MsgBox “please numeric values” Cancel = True End If End Sub Private Sub Txtl_Change() End Sub How can i take to address in outlook way VBA. Regards, Moorthy P I need some help with my excel database. I’ve made a Form which holds two groups of two radiobuttons. The form works nice except for the outcome of these radiobuttons. I want them to say “Afslag” and “Opbod+Afslag” instead of TRUE and FALSE. How can I make it happen? Like to hear from you guys, tnx a lot. Roel I do this (manually) all the time and it’s saved me a lot of trouble having those backups. I use the format “yyyymmdd_hhmm” though because that way I can sort Alphabetically in File Explorer. I might add your function to my library so that I can hot-key it, because there have been times when I’ve been too absorbed in development to remember to save the timestamped copies resulting in the loss of hours of work! Thanks for the tip. If users do prefer, I guess, a more intuitive date format then rather than “dd-mm-yyyy”, a more general coding suited to folk in the US etc would be format(Date,”short date”) which will automatically use the local format (mm-dd-yyyy for our US friends). great idea to have all these potentially useful snippets in one place useful macros in my Personal.xlsb army include: • Toggle centre across cells (merging really is the devil’s spawn) • Clear user-defined styles (I’ve had spreadsheets bloated with over 3000) • Unprotect worksheets (for when you don’t have the password) • Toggle the status bar (to give that one extra line of display) • Cycle case between UPPER, lower, Sentence and Title Case – the latter with selected exceptions (a, the, it, AA, RAC, pH etc) Sub Pwd() On Error Resume Next For i = 65 To 66: For j = 65 To 66: For k = 65 To 66: For l = 65 To 66: For m = 65 To 66: For n = 65 To 66: For o = 65 To 66: For p = 65 To 66: For q = 65 To 66: For r = 65 To 66: For S = 65 To 66: For t = 32 To 126 ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & Chr(n) & Chr(o) & Chr(p) & Chr(q) & Chr(r) & Chr(S) & Chr(t) If ActiveSheet.ProtectContents = False Then MsgBox “Worksheet unlocked, relock with: ” & Chr(i) & Chr(j) & Chr(k) & Chr(l) & ” ” & Chr(m) & Chr(n) & Chr(o) & Chr(p) & ” ” & Chr(q) & Chr(r) & Chr(S) & Chr(t) Exit Sub End If Next: Next: Next: Next: Next: Next: Next: Next: Next: Next: Next: Next End Sub enjoy NB the password found is not THE password, just one that will work and, if used to re-lock will still allow the original password to work This only works for worksheets, not workbooks – though you may find the same password has been used jim
