Excel Macro & Advanced Filter how to use
⚡ Golden Web Tools
✨ Excel Macro · VBA · Advanced Filter ✨
Your complete guide to automating Excel with macros (recording & VBA) and mastering Advanced Filter with real examples.
📼 Macro & VBA record + code
Macro = record actions (no coding). VBA = write custom logic with variables, loops, and conditions.
🎬 1. Record a Macro
- Show Developer tab: File → Options → Customize Ribbon → check Developer.
- Go to Developer → Record Macro.
- Name:
FormatHeader(no spaces). Shortcut: Ctrl+Shift+H. - Store in: This Workbook (so it travels with the file).
- Perform actions: select row 1, bold, center, fill gold.
- Click Stop Recording.
- Run: shortcut Ctrl+Shift+H or Developer → Macros → Run.
📌 Recorded macro – auto‑generated VBA:
Sub FormatHeader()
' Keyboard Shortcut: Ctrl+Shift+H
With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
.Interior.Color = 65535 ' gold/yellow
End With
End Sub
💡 The recorder captures absolute references by default. Use relative references for flexibility.
🧑💻 2. Write VBA Code
Press Alt+F11 to open VBA editor. Insert a module and paste:
Sub FormatInvoice()
' apply formatting to a specific range
With Range("A1:D1")
.Font.Bold = True
.HorizontalAlignment = xlCenter
.Interior.Color = RGB(255, 215, 0) ' gold
End With
End Sub
🔁 VBA loop iterate through rows and apply logic:
Sub ApplyBonus()
Dim i As Long
For i = 2 To 100
If Cells(i, "B").Value > 5000 Then
Cells(i, "C").Value = "Bonus"
End If
Next i
End Sub
🧠 VBA variables & logic – use
Dim, If...Then, For Each, Do While.
⚠️ VBA changes cannot be undone with Ctrl+Z. Always keep a backup.
🔍 Advanced Filter criteria · extract
Filter with complex logic (AND / OR) and copy results to a new location or in‑place.
📋 The 3 Components
- List Range: your data table (must have headers).
- Criteria Range: headers + conditions. Same row = AND, different rows = OR.
- Extract Range: (optional) destination for filtered rows. Only headers needed.
📐 Criteria Rules – detailed
- Same row → AND (e.g. Region = "East" AND Sales > 5000)
- Different rows → OR (e.g. Region = "East" OR Region = "West")
- Wildcards:
*(any characters),?(one character). - Blanks:
==· Non‑blanks:<> - Formula criteria: use a formula (e.g.
=B2>AVERAGE(B:B)). The header in criteria must be blank or different from data header.
⚙️ Step‑by‑step example
Data: Name (A), Region (B), Sales (C) in rows 1–100.
- Set criteria: type header Region in E1, Sales in F1. In E2:
East, in F2:>5000. - Set extract headers: in E5:G5 type
Name,Region,Sales. - Go to Data → Advanced.
- Action: Copy to another location.
- List range:
$A$1:$C$100· Criteria:$E$1:$F$2· Copy to:$E$5. - Click OK → all East sales >5000 appear.
🧩 OR condition (different rows)
Criteria:
→ returns all rows where Region is East or West.
Criteria:
Region → East (row2) Region → West (row3) → returns all rows where Region is East or West.
📌 Advanced Filter with formula
Dynamic criteria using a formula – no hard‑coded values.
= C2 > AVERAGE(C:C) ' sales above the average
✅ Unique records only – check Unique records only in the Advanced Filter dialog to extract distinct values.
⚡ Advanced Filter is a one‑time snapshot. Re‑run to refresh results if data changes.
🧾 Macro Recorder – full process
- Select range (e.g. A1:D1) before recording.
- Developer → Record Macro → name, shortcut, store in “This Workbook”.
- Apply formatting: bold, center, fill colour, borders, etc.
- Stop Recording.
- Test: select another range and press shortcut or run from Macros.
- View code: Alt+F11 → Modules →
FormatHeader.
📋 Relative reference recording – click Use Relative References before recording to make macros work on any selected cell.
📊 Advanced Filter – extra examples
- Extract unique list: Data → Advanced → Unique records only → copy to new location.
- Mixed AND/OR:
Row2:Sales > 5000ANDRegion = East
Row3:Region = West
→ (Sales >5000 AND East) OR West. - Wildcard:
J*in Name column finds “John”, “Jane”, etc. - Case insensitive – Advanced Filter is not case‑sensitive.
🔁 Re‑run filter – if source data changes, simply run Advanced Filter again with the same ranges.
🧩 VBA in action – more examples
📌 Loop with condition
Sub HighlightLowStock()
Dim rng As Range
For Each rng In Range("C2:C100")
If rng.Value < 10 Then
rng.Interior.Color = RGB(255, 0, 0) ' red
End If
Next rng
End Sub
📌 Message box & input
Sub GreetUser()
Dim userName As String
userName = InputBox("Enter your name:")
MsgBox "Hello " & userName & "! Welcome."
End Sub
📋 Advanced Filter – full process with example
1 List Range
$A$1:$C$100 (headers: Name, Region, Sales)2 Criteria Range
E1:F2 → header: Region / Sales row2: East / >5000 (AND condition)
3 Extract Range
$E$5:$G$5 (headers: Name, Region, Sales) – only top row needed.4 Run
Data → Advanced → fill ranges → OK → filtered data appears at E5.
📌 OR with three criteria – add a third row:
Region:
Region:
South → now returns East, West, or South.
✨ Golden Web Tools ✨ — Master Excel automation with Macros (record & VBA) and Advanced Filter.
Built with ❤️ for productivity · all processes shown with real examples.
Built with ❤️ for productivity · all processes shown with real examples.
Shiben
As you came here, so you must go. So don't delay, do the main thing, many real genes will know or understand.
