Excel is a useful program for creating tables, graphs and making complicated calculations automatically. It might seem complicated at the beginning, but when you get to know Excel's features, the program becomes very practical and an indispensable tool. One of its most used features is a macro tool that lets you customize and automate frequent actions. In this article, you will learn what macro is and how to use it in Excel.
What is Macro?
Macro is a handy tool for frequent Excel users that helps to save time and automatically configure some commands. Macro works with VBA (Visual Basic for Application) program codes, but to use them, you don't have to be a programmer at all. All you need to know is just some basic commands and how to use them in practice.
How to enable Macros in Excel?
To use Macros in Excel, you need to first enable the developer option. To do so, follow the instructions below.
- Go to File > Options and choose Customize the Ribbon.
- Next, in the right column checkmark Developer.
- Then click OK. Now you will see a Developer tab in your main menu.
Go to Preferences > View and checkmark the Developer tab in Ribbon, Show section.
How to create Macros?
Now that you have enabled Macros in your Excel file, you can create your own macros if you use a repetitive task in Excel and want to facilitate it.
- To record a macro, go to Developer tab and choose Record Macro.
In the dialog box fill in the gaps:
- In the name section write a name for this macro, choose a shortcut key and the description for the process this macro does.
- Click OK and perform the action you’d like to save.
- When you finish, click Stop Recording. Now your macro is saved and you can use it whenever you want.
How to run Macros?
- To run saved macros, go to Developer tab and select Macros.
- In the pop-up window choose the macro you want to use and click Run.
How to use specific Macro codes on your Excel file?
If you want to perform an action that involves a specific macro code, you can use the instructions and the list of the codes below.
How to add a code to the VB?
- To add a code, open the workbook and press ALT+F11 to open VB Editor or click on Visual Basic in the left corner of the menu.
- In the Project Explorer choose a workbook to which you wish to add a macro.
- Right-click on it and choose Insert > Module.
- Now the new module has to appear in the left tab and you can add a code.
- Select the module and go to the right tab.
- Next, paste the code you need.
- Finally close the window and go back to your workbook. You can find this code in your saved macros. To use this code, follow the instructions on how to run macros outlined above.
List of useful Macro codes for Excel
- To sort the worksheets alphabetically:
Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub
- To unmerge all the merged cells:
Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub
- To save each worksheet as a separate PDF:
Sub SaveWorkshetAsPDF() Dim ws As Worksheet For Each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub
- To lock all the cells with formulas:
Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub
- To automatically insert a row after every row:
Sub InsertAlternateRows() Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow.Insert ActiveCell.Offset(2, 0).Select Next i End Sub
- To highlight the cells that have misspelled words:
Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl End Sub
- To get only the text part from a string:
Function GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1) Next i GetText = Result End Function
- To get only the numeric part from a string:
Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result End Function
- To insert a linked picture:
Sub LinkedPicture() Selection.Copy ActiveSheet.Pictures.Paste(Link:=True).Select End Sub
- To add A-Z alphabets in a range:
Sub addcAlphabets() Dim i As Integer For i= 65 To 90 ActiveCell.Value= Chr(i) ActiveCell.Offset(1, 0).Select Next i End Sub Sub addsAlphabets() Dim i As Integer For i= 97 To 122 ActiveCell.Value= Chr(i) ActiveCell.Offset(1, 0).Select Next i End Sub
- To delete data in the row while keeping the formula Sub KeepFormulas() Dim sRow, lCol As Integer sRow = ActiveCell.Row lCol = Cells(sRow, Columns.Count).End(xlToLeft).Column For Each cell In Range(Cells(sRow, 1), Cells(sRow, lCol)) If cell.HasFormula = False Then cell.ClearContents Next cell End Sub
- Delete a string of character in a range of cells
Option Explicit Option Compare Text Sub DeleteWord() Dim Cel As Range, Range As Range Dim Word As String Set Range = Range("B2:B20") '. Word = "Theword" Application.ScreenUpdating = False For Each Cel In Range If Cel Like "*" & Word & "*" Then Cel = Replace(Cel, Word, "") 'To remove the double space that follows .. Cel = Replace(Cel, " ", " ") End If Next Cel Application.ScreenUpdating = True End Sub
- Combine multiple columns into one Sub test() Dim j As Long, k As Long, r As Range, dest As Range j = Range("A1").End(xlToRight).Column For k = 1 To j Set r = Range(Cells(1, k), Cells(1, k).End(xlDown)) r.Copy Set dest = Cells(Rows.Count, "A").End(xlUp).Offset(3, 0) dest.PasteSpecial Next k End Sub
- Excel vba userform examples
- Excel vba userform examples free download
- Excel online macro
- How to run macro when data entered in a cell > Guide
- I need a macro that can extract data > Forum - Excel
- How to insert blank rows using macros in Excel > Guide
- Unlock password protected Excel file: Macro > Guide
- Run macro on opening: worksheet, workbook > Guide
- Change date format in Excel: to dd/mm/yyyy, mm/dd/yyyy
- Copy data from one excel sheet to another: automatically
- Fix arrows keys in Excel: Scroll lock, alternative method
- How to take screenshot in Excel: shortcut, sheet, cell
- How to apply a function to multiple sheets on Excel
- Convert numbers to words in Excel: without VBA, formula
- How to change author name in Excel: spreadsheet, VBA
- How many IF statements can you nest in Excel
- How to use Excel color cell if formula
- How to display multiple columns in a validation list
- How to use an IF statement to add 1 to total
- How to perform a partial cell match in Excel?
- Check if a value exists in an array VBA
- Search and find using VBA in Excel
- VBA select case like: operator, string, statement
- What is the VBA code to select last sheet in workbook
- VBA Excel color codes: index number, list
- How to insert file path in excel: cell, sheet
- How to disable auto recover in Excel
- How to enable VBA in Excel: Mac, Office 365
- How combine IF function, SEARCH and ISERROR in Excel
- How to enter multiple lines in a single Excel cell
- How to insert GIF in Excel: 365, sheet, VBA
- How to count names in Excel: formula, using COUNTIF
- Open and convert an Excel file in Notepad
- Repeat rows in Excel: based on cell value, VBA
- How to automatically transfer data between sheets in Excel
- How to manipulate data in Excel: VBA
- How to copy a Macro into a blank cell
- Insert a hyperlink in Excel: with text, to another tab
- Recover Excel file: previous version
- How to transfer data from one Excel sheet to another?
- Most useful Excel formulas: for data analysis
- Recalculate Excel Workbook Before Saving
- How to apply if function in Excel: with dates, with text
- Select the default number of processors in Excel
- Insert picture in Excel: cell, shortcut, using formula
- How to add sheet to workbook: VBA, Excel
- Excel functions in French
- How to create a cascading combo box: Excel, VBA
- How to create calculator in Excel VBA
- Excel export data: from one sheet to another
- How to add a number of days to a date in Excel
- How to fill multiple Excel sheets from master sheet
- How to copy data to another workbook: using VBA
- Transfer Excel data from one sheet to another: VBA
- Excel VBA add command button programatically
- How to change a column to numeric in Excel?
- Mark sheet grade formula in Excel: template
- How to connect VB 6.0 with MS Access
- How to remove leading apostrophe in Excel
- How to create UserForm: in Excel, VBA
- Select empty cell in Excel: VBA
- Excel send value to another cell
- Using VBA to find last non empty row: in column, in table
- How to show or hide formula bar in Excel: VBA, shortcut
- How to change enter key function in Excel
- How to use Excel auto numbering formula
- Conditional formatting with dates: in Excel
- How to calculate VAT in Excel: formula
- Transfer a worksheet to another Excel workbook: without VBA
- Delete duplicates in Excel: column, formula
- Send email with attachment Excel: VBA, macros
- How to Insert a Transparent Image into an Excel Document
- Compare two Excel sheets: and combine data
- Copy data from one Excel workbook to another
- Auto generate serial number in Excel: VBA, formula
- How to generate email notifications for Excel updates
- How to create a drop-down List in Excel
- Split a workbook into individual files in Excel
- How to clear formatting in Excel
- Insert an image into an Excel comment box
- Credit summation formula
- Create new sheet based on cell value: Excel, macro
- How to insert multiple rows in Excel with a macro?
- How to change Excel date format
- How to create a timer in Excel VBA
- How to use conditional formatting in Excel
- How to count occurrences of characters and numbers in Excel
- Shortcuts to insert in Excel
- How to copy data to multiple worksheets in Excel
- Excel IF, AND, OR, and NOT functions
- Create a new workbook and copy data in Excel via a Macro
- Update dates in Excel spreadsheet: each year, formula
- How to change row color in Excel VBA based on value