How to set a range as the intersection of UsedRange WITHOUT first row + Another Column?

Question

I have a range variable "rng". I need to set rng to be the intersection of: (1) The usedrange on the sheet EXCLUDING the first column, AND (2) Column number 6, for example

Currently, I have:

Set rng = Intersect(.UsedRange, .Columns(6)).SpecialCells(xlCellTypeVisible)
' Because the range is filtered and i only need to select visible cells

But this returns a column that also has the header row in it. I only need the numbers in the column.

(1) Any quick function/method/property to do that? (2) how do i find the size of this range? rng.rows.count always returns ONE, even though there are multiple cells in rng. Should I use rng.count? what's the difference?

Thank you very much,

Al

Answer 1

I see that you've already accepted an answer, and yet I don't see how it answers your requirement that it doesn't include the header row. Here's my solution which does that. It also answers your question 2 of how to get the row count:

Sub GetRangeAndCountRows()
Dim rng As Excel.Range
Dim rngArea As Excel.Range
Dim RowCount As Long

With ActiveSheet
    Set rng = Intersect(.UsedRange.Resize(.UsedRange.Rows.Count - 1, .UsedRange.Columns.Count).Offset(1, 0), .Columns(6)).SpecialCells(xlCellTypeVisible)
    Debug.Print rng.Address
    For Each rngArea In rng.Areas
        RowCount = RowCount + rngArea.Rows.Count
    Next rngArea
    Debug.Print RowCount
End With
End Sub