excelvbasetintersectheader-row

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


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


Solution

  • 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