OK, I am finishing up an add-on project for a legacy Excel-VBA application, and I have once again run up against the conundrum of the mysterious range.Rows(?) and worksheet.Rows properties.
Does anyone know what these properties really do and what they are supposed to provide to me? (note: all of this probably applies to the corresponding *.Columns properties also).
What I would really like to be able to use it for is to return a range of rows, like this:
SET rng = wks.Rows(iStartRow, iEndRow)
But I have never been able to get it to do that, even though the Intellisense shows two arguments for it. Instead I have to use one of the two or three other (very kludgy) techniques.
The help is very unhelpful (typically so for Office VBA), and googling for "Rows" is not very useful, no matter how many other terms I add to it.
The only things that I have been able to use it for are 1) return a single row as a range ( rng.Rows(i) ) and 2) return a count of the rows in a range ( rng.Rows.Count ). Is that it? Is there really nothing else that it's good for?
Clarification: I know that it returns a range and that there are other ways to get a range of rows. What I am asking for is specifically what do we get from .Rows() that we do not already get from .Cells() and .Range()? The two things that I know are 1) an easier way to return a range of a single row and 2) a way to count the number of rows in a range.
Is there anything else?
I'm not sure, but I think the second parameter is a red herring.
Both .Rows and .Columns take two optional parameters: RowIndex and ColumnIndex. Try to use ColumnIndex, e.g.
Rows(ColumnIndex:=2)
, generates an error for both .Rows and .Columns.My feeling it's inherited in some sense from the
Cells(RowIndex,ColumnIndex)
Property but only the first parameter is appropriate.There is another way, take this as example
All you need to do is to convert your variables
iStartRow
,iEndRow
to a string.It's perhaps a bit of a kludge, but the following code does what you seem to want to do:
Since the .Rows result is marked as consisting of rows, you can "For Each" it to deal with each row individually, like this:
I use this to check attendance in any of a few categories (different columns) for a list of people (different rows).
(And of course you could use .Columns to do a "For Each" over the columns in the range.)
I've found myself using range.Rows for its effects in the Copy method. It copies the height of the rows from the origin to the destination, which is the behaviour I want.
If I had used rngLastRecord.Copy instead of rngLastRecord.Rows.Copy, the row heights would be whatever was there before the copy.
I've found this works: