Written by Allen Wyatt (last updated November 18, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
When using a worksheet, it is not uncommon to hide rows that contain data you don't want displayed at the current time. If you have written a macro that processes the data in the worksheet, you may have wondered how to skip over and not process the rows that you have marked as hidden.
The way you accomplish this is to check the Hidden property of each row. If the property is True, then the row is hidden; if False, then row is visible.
As an example of how this works, assume that you have a worksheet that you use to track clients. Some of these clients are considered active and others inactive. To mark a client as inactive, you hide the row containing the client. At some point, you want to number the active clients, and you want to do it using a macro. The following macro will do the trick for you:
Sub NumberClients() Dim c As Range Dim j As Integer If Selection.Columns.Count > 1 Then MsgBox "Only select the cells you want numbered" Exit Sub End If j = 0 For Each c In Selection If Not c.Rows.Hidden Then j = j + 1 c.Value = j Else c.Clear End If Next c End Sub
To use the macro, simply select the cells in which the numbering will be done. The macro checks, first of all, to make sure you have only selected cells in a single column. Then, it steps through each cell in the selected range. If the row containing the cell is not hidden, then the counter (j) is incremented and stored in the cell. If the row containing the cell is hidden, then the contents of the cell are cleared. The key to this macro is the If ... End If structure that tests the value of the Hidden attribute.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12552) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Skipping Hidden Rows in a Macro.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
How Excel uses templates is different than how Word uses templates. This tip looks at those differences and discusses ...
Discover MoreWant to run a macro when you first select a worksheet? You can do so by using one of the event handlers built into Excel, ...
Discover MoreNeed to know the character code used for a particular character? In a macro you can use the Asc function to determine the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-11-23 12:12:12
J. Woolley
Re. the NumberClients2 macro in my most recent comment below, immediately after the following statement
    Selection.Columns(1).Select
please add the following statement
    If Selection.Cells.Count = 1 Then Exit Sub
The reason for this follows:
The Range.SpecialCells(Type, [Value]) method is supposed to return only the cells in Range that satisfy Type and (optional) Value. But curiously if Range is a SINGLE cell, then ALL worksheet cells satisfying the criteria are returned whenever SOME worksheet cells do not satisfy the criteria. This "bug" does not occur if ALL worksheet cells satisfy the criteria.
Here are three more SpecialCells issues:
1. The method fails silently if it tries to select more than 8,192 non-contiguous cells (or Areas).
2. Use of On Error GoTo might not work as expected; use On Error Resume Next instead.
3. Avoid direct or indirect use of the SpecialCells method in a user-defined function (UDF); see my recent comment here: https://excelribbon.tips.net/T008524_Selecting_Visible_Cells_in_a_Macro.html
2023-11-21 15:50:39
J. Woolley
Here's another version of the Tip's macro:
Sub NumberClients2()
    Dim c As Range, i As Long, n As Long
    On Error GoTo ErrHandler
    Selection.Columns(1).Select
    Selection.ClearContents
    For Each c In Selection.SpecialCells(xlCellTypeVisible)
        n = n + 1
        c.Value = n
    Next c
    'Clear any hidden cells at start of Selection
    Set c = Selection.Rows(1)
    n = c.Row - 1
    For i = 1 To n 'Skipped if n < 1
        Set c = c.Offset(-1)
        If c.Hidden Then c.ClearContents Else Exit For
    Next i
    'Clear any hidden cells at end of Selection
    Set c = Selection.Rows(Selection.Rows.Count)
    n = c.EntireColumn.Rows.Count - c.Row
    For i = 1 To n 'Skipped if n < 1
        Set c = c.Offset(1)
        If c.Hidden Then c.ClearContents Else Exit For
    Next i
ErrHandler:
End Sub
Before starting the macro, select a range of cells for the active client numbers. After clearing the selection's first column of formulas and values (but not formatting), unhidden rows will be sequentially numbered. As suggested by Alex Blakenburg, "any hidden rows at the start or end of the selection" will also be cleared.
2023-11-18 06:55:57
Alex Blakenburg
Since we are also clearing previously marked rows that are now potentially hidden, we need to include any hidden rows at the start or end of the selection range. Assuming the table or list has a heading something like the below might work.
Note: An alternative method using Selection.SpecialCells(xlCellTypeLastCell) would suffer a similar issue of missing hidden rows above or below the Selection area.
Sub NumberClients_CurrentRegion()
Dim c As Range
Dim j As Integer
Dim rng As Range
If Selection.Columns.Count > 1 Then
MsgBox "Only select the cells you want numbered"
Exit Sub
End If
Set rng = Intersect(Selection.CurrentRegion, Selection.EntireColumn)
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
j = 0
For Each c In rng
If Not c.Rows.Hidden Then
j = j + 1
c.Value = j
Else
c.Clear ' Could be done on the whole rng at once before entering the for loop
End If
Next c
End Sub
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments