What does the Excel range Rows property really do

Question

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

User · Accepted Answer

Range Rows and Range Columns return essentially the same Range except for the fact that the new Range has a flag which indicates that it represents Rows or Columns  This is necessary for some Excel properties such as Range Count and Range Hidden and for some methods such as Range AutoFit      Range Rows Count returns the number of rows in Range  Range Columns Count returns the number of columns in Range  Range Rows AutoFit   autofits the rows in Range  Range Columns AutoFit   autofits the columns in Range    You might find that Range EntireRow and Range EntireColumn are useful  although they still are not exactly what you are looking for  They return all possible columns for EntireRow and all possible rows for EntireColumn for the represented range   I know this because SpreadsheetGear for  NET comes with  NET APIs which are very similar to Excel s APIs  The SpreadsheetGear API comes with several strongly typed overloads to the IRange indexer including the one you probably wish Excel had    IRange this int row1  int column1  int row2  int column2     Disclaimer  I own SpreadsheetGear LLC

User · Answer

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

User · Answer

There is another way  take this as example  Dim sr As String     sr    6 10  Rows sr  Select   All you need to do is to convert your variables iStartRow  iEndRow to a string

User · Answer

Your two examples are the only things I have ever used the Rows and Columns properties for  but in theory you could do anything with them that can be done with a Range object   The return type of those properties is itself a Range  so you can do things like   Dim myRange as Range Set myRange   Sheet1 Range Cells 2 2  Cells 8 8   myRange Rows 3  Select   Which will select the third row in myRange  Cells B4 H4 in Sheet1    update  To do what you want to do  you could use   Dim interestingRows as Range Set interestingRows   Sheet1 Range startRow  amp       amp  endRow    update  2  Or  to get a subset of rows from within a another range   Dim someRange As Range Dim interestingRows As Range  Set myRange   Sheet1 Range Cells 2  2   Cells 8  8    startRow   3 endRow   6  Set interestingRows   Range myRange Rows startRow   myRange Rows endRow

User · Answer

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   rngLastRecord Rows Copy Destination  Sheets  Availability   Range  a   amp  insertRow    If I had used rngLastRecord Copy instead of rngLastRecord Rows Copy  the row heights would be whatever was there before the copy

User · Answer

Since the  Rows result is marked as consisting of rows  you can  For Each  it to deal with each row individually  like this   Function Attendance rng As Range  As Long Attendance   0 For Each rRow In rng Rows     If WorksheetFunction Sum rRow   gt  0 Then         Attendance   Attendance   1     End If Next End Function   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

User · Answer

I ve found this works   Rows CStr iVar1   amp       amp  CStr iVar2   Select

User · Answer

It s perhaps a bit of a kludge  but the following code does what you seem to want to do   Set rng   wks Range wks Rows iStartRow   wks Rows iEndRow   Rows

User · Answer

Range Rows  Range Columns and Range Cells are Excel Range objects  according to the VBA Type   functions    TypeName Selection rows  Range  However  that s not the whole story  those returned objects are extended types that inherit every property and method from Excel  Range - but  Columns and  Rows have a special For    Each iterator  and a special  Count property that aren t quite the same as the parent Range object s iterator and count   So  Cells is iterated and counted as a collection of single-cell ranges  just like the default iterator of the parent range   But   Columns is iterated and counted as a collection of vertical subranges  each of them a single column wide      And  Rows is iterated and counted as a collection of horizontal subranges  each of them a single row high   The easiest way to understand this is to step through this code and watch what s selected   Public Sub Test    Dim SubRange As Range Dim ParentRange As Range  Set ParentRange   ActiveSheet Range  B2 E5     For Each SubRange In ParentRange Cells     SubRange Select Next   For Each SubRange In ParentRange Rows     SubRange Select Next  For Each SubRange In ParentRange Columns     SubRange Select Next  For Each SubRange In ParentRange     SubRange Select Next  End Sub  Enjoy  And try it with a couple of merged cells in there  just to see how odd merged ranges can be

[vba] What does the Excel range.Rows property really do?

Examples related to vba

Examples related to excel

Examples related to ole-automation