Trouble Using a Named Range with Rows( )

Question

Hello All! I'm a Visual Basic "newbie" - just 2 days into my Visual Basic experience.

I have a need to hide and/or unhide a range of rows at the bottom of a filtered table. The following subroutines using specific Row references works great - except that the range of rows changes dynamically as rows are added / deleted from the data.

I've created a "Named Range" called xHidden using the Name Manager. I've confirmed that the range of rows change appropriately as rows are added or deleted from the data (by looking at range defined in the Name Manager before and/or after adding and/or deleting rows from the data.

Both routines execute as desire using the specific row references shown in the code snippets below. However, replaceing the specific row reference with named range identifier "xHidden" immediately errors out. I've tried it as Rows("xHidden").Select as well as Rows(xHidden).Select which both fail. What am I missing?

Sub HideHidden()

Sheets("Transaction Log").Select

Rows("$143:$159").Select

Selection.EntireRow.Hidden = True

End Sub

Sub ShowHidden()

Sheets("Transaction Log").Select

Rows("$143:$159").Select

Selection.EntireRow.Hidden = False

Range("A1").Select

End Sub

Answer 1

To use a named range like xHidden with the Rows() method in your VBA code, you need to reference the named range correctly. The Rows() method does not accept named ranges directly as a string. Instead, you can use the Range() method to refer to the named range. Here’s how you can modify your subroutines:

Sub HideHidden()
    Sheets("Transaction Log").Select
    Range("xHidden").EntireRow.Hidden = True
End Sub

Sub ShowHidden()
    Sheets("Transaction Log").Select
    Range("xHidden").EntireRow.Hidden = False
    Range("A1").Select
End Sub

In this code, Range("xHidden") will correctly reference the named range you created, and then you can set the EntireRow.Hidden property as desired. This should resolve the errors you were encountering when trying to use the named range with Rows().

---

References:

• Cannot display specified name because it is hidden
• Refer to Cells by Using Shortcut Notation