VBA & Macro Problems

How to Select Visible Blank Cells After AutoFilter in Excel VBA

Adam Davis Oct 20, 2026 868 views

Question details

The user wants to use an Excel VBA macro to filter an entire column for blank cells, select only the visible rows, and populate those specific cells with formulas.

How to Move to Visible Blank Cells After Applying an AutoFilter in Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Writing a VBA macro to apply an AutoFilter to a dataset and perform operations (like inserting a formula) exclusively on the visible, filtered cells.
Observed behavior: Using the ActiveCell.Offset method incorrectly selects hidden or filtered rows because it does not distinguish between visible and non-visible states, causing formulas to be inserted into the wrong rows.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that the Developer tab is active on your ribbon so you can easily access the VBA Editor.

Solution 1Recommended

Use SpecialCells(xlCellTypeVisible) to Target Filtered Rows

By defining the AutoFilter range and applying the SpecialCells method, you can accurately target only the currently visible cells on the screen and bypass all hidden rows.

The ActiveCell.Offset function simply counts rows incrementally. It does not skip rows that are hidden by an AutoFilter or manually collapsed, which makes it unreliable for filtered datasets. To interact solely with the filtered results, you must explicitly tell VBA to look for visible cells using the SpecialCells(xlCellTypeVisible) method.

This approach efficiently captures the non-contiguous range of filtered cells, allowing you to write relative formulas to all visible blank cells in a single line of code, or iterate through them sequentially if complex logic is needed.

Open the VBA Editor

Press Alt + F11 in your Excel workbook to open the Visual Basic for Applications Editor. Navigate to 'Insert' > 'Module' to create a new script area.

Apply the AutoFilter

Write your VBA code to target the specific worksheet and apply the filter for blank cells. For example: FilBsDt.Range("A1:G1000").AutoFilter Field:=3, Criteria1:="="

Define the Visible Range

Using a With statement on the filtered column, offset by 1 row to skip the header, then resize it and apply the visible cells method: Set rngBlanks = .Offset(1, 0).Resize(.Rows.Count - 1, 1).Cells.SpecialCells(xlCellTypeVisible)

Populate with Formulas

Apply your desired formula to the entire range simultaneously. For example: rngBlanks.FormulaR1C1 = "=RC[2]+RC[4]" to dynamically calculate based on the relative columns of each row.

Use SpecialCells(xlCellTypeVisible) to Target Filtered Rows

Error Handling Recommendation: Always include 'On Error Resume Next' before defining the SpecialCells range. VBA will throw a runtime error if no visible blank cells are found after the filter is applied.

VBA-Compatible Spreadsheet Solution

Run AutoFilter Macros Seamlessly in WPS Spreadsheet

WPS Office offers comprehensive VBA support in its advanced versions, allowing you to execute Microsoft Excel macros—including AutoFilter and SpecialCells operations—flawlessly. You can manage complex datasets without needing to modify your existing VBA scripts.

1. Open Your Workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsm file that contains the dataset you need to filter.
2. Access the VBA Editor: Navigate to the Developer tab on the top ribbon and click 'VBA Editor', or simply press Alt + F11.
3. Execute the AutoFilter Macro: Paste your SpecialCells code into a module, then click 'Run' or press F5 to execute the filter and populate your visible blank cells instantly.

Fully compatible with Microsoft Excel VBA and macro formats (.xlsm, .xlsb).High performance and stability when applying AutoFilters on massive datasets.Familiar Developer tab interface making code migration and editing effortless.Lightweight application that loads faster and consumes fewer system resources.

Frequently Asked Questions

Why does ActiveCell.Offset move to hidden rows instead of visible ones?

ActiveCell.Offset calculates the destination by counting the absolute number of rows from the starting point in the worksheet's grid. It does not evaluate whether those rows are currently visible or hidden by a filter, which causes it to mistakenly select rows that are not part of your filtered results.

How can I apply a specific custom formula to each filtered cell individually?

Instead of applying the formula to the entire range in one line, you can use a 'For Each' loop to iterate through the visible range (e.g., For Each rngCel In rngBlanks). Inside the loop, you can extract the row number using rngCel.Row and dynamically assign custom formulas like rngCel.Formula = "=E" & lngRow & "+G" & lngRow.

What happens to the macro if there are no blank cells to filter?

If the AutoFilter results in zero blank cells, attempting to set a range with SpecialCells(xlCellTypeVisible) will trigger a runtime error in VBA. To prevent the macro from crashing, implement error handling such as placing 'On Error Resume Next' before setting the range, and checking if the range is 'Nothing' before applying formulas.

Excel & Data Problems