We all know Excel is the Powerhouse of Data analysis. We know Excel is a ground of repetitive tasks and no one wants to do repetitive things right?? We’re here with the magic for automating these tasks and that is, Macros in Excel.
Macros in Excel can be considered as a set of programming instructions that can be recorded, named, saved, and executed in VBA as and when required macros can help us to automate repetitive tasks associated with data manipulation that must be accomplished repeatedly. You can also enable and disable macros in Excel.
Mastering a few key macros can save you countless hours and boost your productivity. In this blog post, we’ll learn the top 20 most useful Macros In Excel that you should start using today.
Want to Excel using Excel in just 3 hours??? If yes, then join Excel Using AI workshop by Office Master today!!1
Table of Contents
The Top 20 Macros in Excel
1. Delete Blank Cells in Row 1
This macro identifies and deletes any blank cells in the first row of your worksheet, then shifts the remaining cells up to fill the gaps. It’s simple but will surely help you clean up your data.
vb
Sub DeleteBlankCellsinRow1()
Rows(“1:1”).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Sub
2. Sort Single Column
Sorting data in Excel can be time taking, but this macros in excel makes it way more easier. It allows you to sort a range of cells by a single column, in either ascending or descending order.
vb
Sub SortSingleColumn()
Range(“A1:K250”).Sort Key1:=Range(“C1”), Order1:=xlDescending
End Sub
3. Sort Multiple Columns
Previous macros can be considered as the base for this one as, this one lets you sort by multiple columns. You can specify the order and direction for each column, making it easy to organize complex data sets.
vb
Sub SortMultipleColumns()
Range(“Sales”).Sort Key1:=Range(“D1”), Order1:=xlAscending, Key2:=Range(“J1”), Order2:=xlDescending
End Sub
4. Clear All Hyperlinks
If you work with a lot of hyperlinked data, this macro can save you a lot of time. It clears all the hyperlinks on the active worksheet, leaving the underlying text or values intact.
vb
Sub ClearHyperlinks()
ActiveSheet.Hyperlinks.Delete
End Sub
5. Copy Data to New Workbook
This macro takes a selected range of data and copies it to a new workbook, which it then saves with a user-specified name. It’s a quick way to extract and share important information.
vb
Sub CopyToNewWorkbook()
Dim newWb As Workbook
Dim newFileName As String
newFileName= Application.GetSaveAsFilename(InitialFileName:=”Extracted Data”, _
FileFilter:=”Excel Workbooks (*.xlsx), *.xlsx”)
If newFileName <> “False” Then
Set newWb = Workbooks.Add
Selection.Copy
newWb.Sheets(1).Range(“A1”).PasteSpecial Paste:=xlPasteValues
newWb.SaveAs Filename:=newFileName
newWb.Close
End If
End Sub
6. Unhide All Worksheets
This macro can help you unlock sheets in a worksheet. It quickly unhides all the worksheets in the active workbook, making it easy to navigate and work with your data.
vb
Sub UnhideAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = True
Next ws
End Sub
7. Freeze Row/Columns
Freezing rows or columns in Excel can be a useful way to keep important information visible as you scroll through a worksheet. This macro automates the process, allowing you to quickly freeze the top row and/or leftmost column.
vb
Sub FreezePanes()
ActiveWindow.FreezePanes = True
ActiveWindow.SplitColumn = 1
ActiveWindow.SplitRow = 1
End Sub
So, enjoying it so far or getting worried about how will you remember all these formulas?? What if there’s no need of remembering all these and that can happen if you join our 3-hour online workshop on excel by the office master.
Do you know?? There are ways by which AI in Excel can streamline your workflow and save up a lot of your time, even upto 3-4 hours daily!! Yes, that’s absolutely correct. Want to know how?? Then read the full blog here.
8. Protect Worksheet
Now keep your worksheet protected with the help of this macro, especially if you’re sharing your workbook with others. This macro allows you to quickly apply password protection to the active worksheet.
vb
Sub ProtectWorksheet()
Dim password As String
password = InputBox(“Enter a password to protect the worksheet:”)
ActiveSheet.Protect password:=password
End Sub
9. Unprotect Worksheet
Key for the previous macro, this one removes the password protection from the active worksheet, making it easier to edit and update the data.
vb
Sub UnprotectWorksheet()
Dim password As String
password = InputBox(“Enter the password to unprotect the worksheet:”)
ActiveSheet.Unprotect password:=password
End Sub
10. Copy Worksheet to New Workbook
Sometimes you need to extract a specific worksheet from a workbook and share it as a single or separate file. This macro automates the process, creating a new workbook with the selected worksheet.
vb
Sub CopySheetToNewWorkbook()
Dim newWb As Workbook
Dim newFileName As String
newFileName = Application.GetSaveAsFilename(InitialFileName:=ActiveSheet.Name, _
FileFilter:=”Excel Workbooks (*.xlsx), *.xlsx”)
If newFileName <> “False” Then
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=newFileName
ActiveWorkbook.Close
End If
End Sub
11. Transpose Data
Transposing data in Excel can be a difficult and time taking task, but this macros in excel makes it easier for you. It takes the selected range and flips the rows and columns, saving you time and effort.
vb
Sub TransposeData()
Selection.Copy
Worksheets.Add.Range(“A1”).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub
12. Apply Conditional Formatting
Conditional formatting is a powerful tool for highlighting important data in your worksheets. This macro allows you to quickly apply a set of predefined formatting rules to a selected range.
vb
Sub ApplyConditionalFormatting()
With Selection.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:=”=100″)
.Interior.Color = vbRed
.Font.Color = vbWhite
End With
End Sub
13. Create Pivot Table
Pivot tables are essential for analyzing and summarizing large data sets, but setting them up can be time-consuming. This macro automates the process, allowing you to quickly create a pivot table from a selected data range.
vb
Sub CreatePivotTable()
Dim pvtCache As PivotCache
Dim pvtTable As PivotTable
Dim rng As Range
Set rng = Range(“A1:F100”)
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng)
Set pvtTable = pvtCache.CreatePivotTable(TableDestination:=Range(“H5″), TableName:=”SalesPivotTable”)
With pvtTable.PivotFields(“Product”)
.Orientation = xlRowField
.Position = 1
End With
With pvtTable.PivotFields(“Sales”)
.Orientation = xlDataField
.Position = 1
End With
End Sub
14. Generate Random Numbers
Sometimes you need to generate random numbers for testing or simulation purposes. This macro creates a range of random numbers within a specified minimum and maximum value.
vb
Sub GenerateRandomNumbers()
Dim rng As Range
Dim minValue As Integer, maxValue As Integer
Dim i As Long
Set rng = Range(“A1:A100”)
minValue = 1
maxValue = 100
For i = 1 To rng.Rows.Count
rng(i, 1).Value = Application.WorksheetFunction.RandBetween(minValue, maxValue)
Next i
End Sub
15. Merge Cells
Merging cells in Excel can be a tedious task, especially if you need to do it repeatedly. This macro simplifies the process, allowing you to quickly merge a selected range of cells.
vb
Sub MergeCells()
Selection.Merge
End Sub
16. Unmerge Cells
Complementing the previous macro, this one unmerged any merged cells in the selected range, restoring the original cell structure.
vb
Sub UnmergeCells()
Selection.UnMerge
End Sub
17. Insert Hyperlink
Manually inserting hyperlinks in Excel can be a time-consuming process. This macros in excel streamlines the task, allowing you to quickly create a hyperlink from a selected cell to a specified URL.
vb
Sub InsertHyperlink()
Dim hyperlinkText As String, hyperlinkAddress As String
hyperlinkText = InputBox(“Enter the text for the hyperlink:”)
hyperlinkAddress = InputBox(“Enter the hyperlink address:”)
ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=hyperlinkAddress, _
TextToDisplay:=hyperlinkText
End Sub
You can use more such formulas and also Learn to create stunning excel dashboards in just Rs. 99 by joining our online Excel using AI workshop by Office Master.
18. Format Cells as Currency
Formatting cells as currency can be a repetitive task, especially if you need to do it for a large range of data. This macro automates the process, quickly applying the appropriate currency formatting to the selected cells.
vb
Sub FormatAsCurrency()
Selection.NumberFormat = “$#,##0.00”
End Sub
19. Format Cells as Percentage
Similar to the previous macro, this one formats the selected cells as percentages, making it easier to work with and analyze data that represents proportions or ratios.
vb
Sub FormatAsPercentage()
Selection.NumberFormat = “0.00%”
End Sub
20. Highlight Alternate Rows
Highlighting every other row in a worksheet can make it easier to read and understand the data. This macro applies a simple conditional formatting rule to achieve this effect.
vb
Sub HighlightAlternateRows()
Dim rng As Range
Set rng = Selection
rng.FormatConditions.Add Type:=xlExpression, Formula1:=”=MOD(ROW(),2)=0″
rng.FormatConditions(rng.FormatConditions.Count).Interior.Color = RGB(240, 240, 240)
End Sub
Conclusion
In this blog we have learned 20 Macros in Excel that can literally help you make your life and work much easier. Each macros have their own use and will vary according to our needs.
We have also seen how we can learn all of this in just 3 hours by joining Office Master’s Workshop in just 99/-
Follow us on Instagram, Facebook and LinkedIn to Learn more about Excel!!
