0

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

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Alaa Elwany
  • 677
  • 7
  • 15
  • 20

2 Answers2

2

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
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • I saw that wording two about the header row, but I thought since the OP was insistent on the column, he just wanted to chop off the first column. But it's funny, because it actually does the same thing as the original line, in that it selects all used cells in Column F. I ignored it at first, but you make a good point. – Scott Holtzman Jun 28 '12 at 19:49
  • Thanks Scott and Doug. It took me some experimentation until I figured out that I need more to it than the first answer by Scott. However, still both Scott's and and Doug's answer did provide me with insight on how to resolve a big part of the problem (I am an average VBA user but not an absolute beginner, so sometimes even if the post does not 100% answer my question, it gives me insight as of how to modify it and use it efficiently). – Alaa Elwany Jun 29 '12 at 13:56
1

Tim's comment above works well, of course.

This answer is to keep in line with the code above, and also may be easier to read.

Add .UsedRange.Offset(,1) instead of .UsedRange to ignore the first column in the UsedRange in your Interesect formula.:

Set rng = Intersect(.UsedRange.Offset(,1), .Columns(6)).SpecialCells(xlCellTypeVisible)
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • 1
    Note that this will fail with a runtime error if there's something in the last column. – Doug Glancy Jun 28 '12 at 19:39
  • Actually, after more thought I think you really want this, if you are trying to get rid of your header row `Set rng = Intersect(.UsedRange,.UsedRange.Offset(1), .Columns(6)).SpecialCells(xlCellTypeVisible)` This gives you essentially F2:F(lastrow in used range). I left this in a comment, since you've accepted the answer... (which is mainly why I ignored it after I caught it.) – Scott Holtzman Jun 28 '12 at 19:52
  • 1
    Just be aware that this will fail if there's something in the last row, similar to my comment above. Generically, if there's something in the last row (or column) and you do an Offset(1,) (or (,1)) you are attempting to create a range that's beyond the last row (or column) of the worksheet. That's why you need to do a Resize of one less row (or column) before the Offset. – Doug Glancy Jun 28 '12 at 20:16
  • D'uh! You would think I would have noted that, seeing your first comment. Not a bright day for me, today :) Thank you for patience and explanation. It definitely helps. I don't often use Resize, but was thinking I need to discover it more. – Scott Holtzman Jun 28 '12 at 20:48
  • 1
    You bet! It's great that we've got this excellent forum to share what we know. – Doug Glancy Jun 28 '12 at 21:01