Excel Macros for Beginners: Recording, Clean Up, and Reset Buttons

What Is A Macro?

Looking to Microsoft to answer this question that many of us find confusing and complex: What is a macro? A macro is an action, or a set of actions, that you can run as many times as you want to automate daily actions or tasks you may perform in Excel.

There are a few ways to use macros, such as recording one and using form controls like inserting a button or combo box. For this post, we will focus on recording a macro and creating a “reset” button to clear data in a section of your workbook. I learned how to do this recently and find this so helpful!

When Macros Make Sense (And When To Keep It Simple)

First, if you’re not into learning to read & sift through code, I would stick to simple operations using Excel’s macros for now. This includes formatting data, deleting & moving around rows, etc. Excel’s macro recording tool records every click, so things can get messy quickly.

However, if you aren’t interested in actually spending the time to understand how Excel’s code works, using AI, you can ask for help debugging the code, and it can help break it down into more manageable chunks if you need something more complex than what this post can offer right now.

Recording A Macro

To record a macro, you must enable the developer tab. You can find this by navigating to File > Options > Customize Ribbon, then checking “Developer”. You can now record your macro and see all the advanced options to help with data formatting and manipulation much more easily.

After recording, if you clicked around a bunch, your macro might fail or have issues when you try to run it. Most macros after recording require at least a few tweaks to run smoothly & not be as fragile or breakable.

Removing Extra Fluff From Your Macro Code

• Recorded macros have a lot of fluff. Code you want usually contains things such as:
  • Range, currentregion, end, sort, autofilter, copy/paste special, entirerow.delete (look for actions). You can use Ctrl+F to help search for these.
  • Delete “select” or “activate” lines.
  • Example: sheets(“Sheet1”).Select, Range(“A1”).Select     Selection.Sort…
  • Rewrite: Sheets(“Sheet1”).Range(“A1”).CurrentRegion.Sort…
• Anchor code to data, not ranges
  • Example: Range(“A1:H200”).Sort…
  • This will break when your dataset exceeds 200 rows
  • For a block of data: Range(“A1”).CurrentRegion (grabs data around A1 until it hits blanks in any direction, best for tables or rectangular data)
  • If column A always has data: Cells(Rows.Count, “A”).End(x1Up).Row ((good anchor for finding the last row in a data set))
• Plan out your code by writing down the steps or actions you want your macro to perform, & then editing your code to match those actions
• Name columns to make referencing them within your code easier

Some Common Code And What It Means

• “Dim ws As Worksheet”
  • Create a variable to represent a worksheet
• “Set ws = ThisWorkbook.Worksheets(“Sheet1”)”
  • ws now refers to the sheet called “Sheet1”
• “Dim lastRow As Long”
  • Create a variable to store a row number
• “lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row”
  • Find the last filled row in column A
• “Dim rng As Range”
  • Create a variable to represent a block of cells
• “Set rng = ws.Range(“A1:H” & lastRow)”
  • Set rng to A1 through H(lastRow)
• “rng.Sort Key1:=ws.Range(“D1”), Order1:=xlAscending, Header:=xlYes”
  • Sort the table by column D (for example)

Quick “Red Flag” Lines To Remove 90% Of Clutter

You can usually delete these without thinking:

• ActiveWindow.ScrollRow = …
• ActiveWindow.ScrollColumn = …
• ActiveWindow.Zoom = …
• Application.CutCopyMode = False (not always harmful, just noise)
• Repeated Select/Activate chains
• Selection references (replace with explicit ranges)

Creating A Button

Creating a button was actually much easier. I used this to clear a portion of a template I made without clearing the formula side, & the automation just makes things so much easier when using the template for deal posting. To create a button for clearing contents on a portion of a data set:

1. Pick the range you want to clear.
2. Open the VBA editor.
3. Insert your macro.
4. Insert the button.

Example:

• Sheet name: Sheet1
• Example data set: B2:G:450

VBA Editor:

• Alt + F11
• Insert > Module

Macro:

“Sub ResetData()

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(“Sheet1”)

ws.Range(“B2:G:450”.ClearContents

End Sub”

Inserting A Button

• Developer tab > Insert
• Form Controls > Button
• Click and drag to draw a button
• Assign Macro ResetData

These few simple automation tools can make your daily tasks much easier, especially if they’re repetitive and rely on data formatting for readability and use. There are parts of macros that are still very confusing to me as well, but we can explore them together in manageable chunks!