VBA - Range Row Count

Question

I have written a simple code to illustrate my predicament    Sub test       Dim sh As Worksheet     Set sh   ThisWorkbook Sheets  Sheet1        Dim k As Long      k   sh Range  A1   sh Range  A1   End xlDown   Rows Count End Sub   What happens is this  We count the rows which contain values starting at A1  If the number of rows which contain values is   1 the code works great  However  if A1 is the only cell which contains any value  k   1 048 576 which I guess is the maximum number of rows allowed in Excel   Why doesn t k   1   Pictures       EDIT  The workaround that I m using is the following   Sub test       Dim sh As Worksheet     Set sh   ThisWorkbook Sheets  Sheet1        Dim k As Long      k   sh Range  A1   sh Range  A1   End xlDown   Rows Count     If k   1048576 Then         k   1     End If     MsgBox  k  End Sub   Since k is always equal to 1048576 when the number of rows with values is 1  It just feels a bit silly having to do something like this

User · Answer

This works for me especially in pivots table filtering when I want the count of cells with data on a filtered column  Reduce k accordingly  k - 1  if you have a header row for filtering   k   Sheets  Sheet1   Range   A  A   SpecialCells xlCellTypeVisible  SpecialCells xlCellTypeConstants  Count

User · Answer

That is nice question     When you have situation with 1 cell  A1   it is important to identify if second declared cell is not empty  sh Range  A1   End xlDown    If it is true it means your range got out of control    Look at code below   Dim sh As Worksheet Set sh   ThisWorkbook Sheets  Arkusz1    Dim k As Long  If IsEmpty sh Range  A1   End xlDown     True Then     k   1  Else     k   sh Range  A1   sh Range  A1   End xlDown   Rows Count  End If

User · Answer

The best solution is to use  Cells Rows Count  1  End xlUp  Row   since it counts the number of cells until it finds the last one written   Unlike  Range  A1   sh Range  A1   End xlDown   Rows Count   what it does is select an  from-to  range and display the row number of the last one busy  A range implies two minimum values  so     meanwhile A1 has a value of the range continues to count to the limit  1048576  then it is shown   Sub test    Dim sh As Worksheet Set sh   ThisWorkbook Sheets 1  Dim k As Long      k   Cells Rows Count  1  End xlUp  Row     MsgBox k      k   sh Range  A1   sh Range  A1   End xlDown   Rows Count     MsgBox k  End Sub

User · Answer

Probably a better solution is work upwards from the bottom   k sh Range  A1048576   end xlUp  row

User · Answer

Have you tried  -  Sub test        k   Cells Rows Count   A   End xlUp  Row      MsgBox  k   End Sub   The  only  catch is that if there is no data it still returns 1

User · Answer

You should use UsedRange instead like so   Sub test       Dim sh As Worksheet     Dim rn As Range     Set sh   ThisWorkbook Sheets  Sheet1        Dim k As Long      Set rn   sh UsedRange     k   rn Rows Count   rn Row - 1 End Sub   The   rn Row - 1 part is because the UsedRange only starts at the first row and column used  so if you have something in row 3 to 10  but rows 1 and 2 is empty  rn Rows Count would be 8

User · Answer

CountRows   ThisWorkbook Worksheets 1  Range  A A   Cells SpecialCells xlCellTypeConstants  Count

User · Answer

k   sh Range  A2   sh Range  A1   End xlDown   Rows Count   or  k   sh Range  A2   sh Range  A1   End xlDown   Cells Count   or  k   sh Range  A2   sh Range  A1   End xlDown   Count

User · Answer

You could also use the  Last  function from Ron de Bruin  http   www rondebruin nl win s9 win005 htm   it worked perfectly for me and also gives back the last column and cell if you want  To get the last row use it like  lastRow   Last 1 yourRange    I found this quite handy   Function Last choice As Long  rng As Range   Ron de Bruin  5 May 2008   1   last row   2   last column   3   last cell     Dim lrw As Long     Dim lcol As Long      Select Case choice      Case 1          On Error Resume Next         Last   rng Find What                                 After  rng Cells 1                             Lookat  xlPart                            LookIn  xlFormulas                            SearchOrder  xlByRows                            SearchDirection  xlPrevious                            MatchCase  False  Row         On Error GoTo 0      Case 2          On Error Resume Next         Last   rng Find What                                 After  rng Cells 1                             Lookat  xlPart                            LookIn  xlFormulas                            SearchOrder  xlByColumns                            SearchDirection  xlPrevious                            MatchCase  False  Column         On Error GoTo 0      Case 3          On Error Resume Next         lrw   rng Find What                                After  rng Cells 1                            Lookat  xlPart                           LookIn  xlFormulas                           SearchOrder  xlByRows                           SearchDirection  xlPrevious                           MatchCase  False  Row         On Error GoTo 0          On Error Resume Next         lcol   rng Find What                                 After  rng Cells 1                             Lookat  xlPart                            LookIn  xlFormulas                            SearchOrder  xlByColumns                            SearchDirection  xlPrevious                            MatchCase  False  Column         On Error GoTo 0          On Error Resume Next         Last   rng Parent Cells lrw  lcol  Address False  False          If Err Number  gt  0 Then             Last   rng Cells 1  Address False  False              Err Clear         End If         On Error GoTo 0      End Select End Function

User · Answer

In case anyone looks at this again  you can use this     Sub test       Dim sh As Worksheet     Set sh   ThisWorkbook Sheets  Sheet1        Dim k As Long     k   sh Range  A1   sh Range  A1   End xlDown  End xlDown  End xlUp   Rows Count End Sub

[vba] VBA - Range.Row.Count

Examples related to vba

Examples related to excel