Counting Rows 3

[yumbelie]

Hi, what would be the best way to count the number of rows (with data) in a spreadsheet? Currently, I use:

'First 4 rows are headers and misc data
Do
rowcount = rowcount + 1
Loop Until MySheet.Cells(rowcount + 4, 1).Value = ""
rowcount + 3 'Return correct number of rows

Problem is, this method is horribly horribly slow, and it was okay while their were only a few rows, but when start getting a lot, the PC will just start hanging while it works it all out. I've seen reference to a 'Count' object, but I could only find it reffered to as returning the number of open workbooks, which wasn't much use :\ - Any help appericated guys!.

Joseph.

 

[DaleWatson123321]

Joseph,

The following rountine will give you the number of rows...

Sub Count_Rows()
Application.Goto Reference:="R5C1"
Firstcell = ActiveCell.Row
Lastcell = [A65536].End(xlUp).Row
numrows = Lastcell - Firstcell + 1
MsgBox numrows
End Sub


Hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[yumbelie]

Fantastic - A shiny purple star for you - Cheers, tho could you explain the code in the Lastcell argument, I'm not sure I understand how it's working.

Many thanks

 

[DaleWatson123321]

Joseph,

Thanks for the STAR - it's appreciated.

As for: Lastcell = [A65536].End(xlUp).Row
This line caused Excel to do an "end-up" from the last row in column A, and assign the Row number to the variable Lastcell.

The term &quot;end-up&quot; means that Excel will go up until it reaches a cell that contains data. This action is the very same as if you manually hit the <End> key, followed by the <Up Arrow> key - except of coure that in this case the line asks for the Row number.

As you might already know, if an <End> <Up Arrow> is used when the cursor is on a range-of-cells, it will go up until it reaches the last cell of that range-of-cells.

This of course also works for the other directions. Here's another variation...

Sub Go_Down()
Range(&quot;A1&quot.End(xlDown).Select
End Sub


The following example is another variation that will demonstrate how you can assign a &quot;range name&quot; to a block of cells.

Assigning range names can be EXTREMELY useful. For example, in writing VBA code, one should whenever possible use range names instead of &quot;hard code&quot; such as A4:K157. This is because &quot;internally&quot; Excel maintains the connection between the names and the cell coordinates. Therefore, whenever adjustments are required, such as inserting/deleting rows, or moving data from place to place, one does NOT have to adjust the VBA code.

For example, if your application has formulas that reference cells via use of range names, any code that is activated to re-adjust the coordinates of any range name(s), will cause the formulas to automatically reflect the change(s).

A common example is a &quot;data list&quot; - or &quot;database&quot; - where each day the list increases in size.

By using range names, you can more easily identify blocks of data for various reasons such as copying or formatting the range of data, or selectively extracting records to a separate sheet for generating regular reports.

The following example re-sets the range name &quot;data&quot; on a sheet named &quot;Database&quot;...

Sub Set_data()
Worksheets(&quot;Database&quot.Select
Application.Goto Reference:=&quot;R4C1&quot;
Firstcell = ActiveCell.Address
Lastcell = [A65536].End(xlUp).Offset(0, 8).Address
datalist = Firstcell & &quot;:&quot; & Lastcell
Range(datalist).Name = &quot;data&quot;
End Sub

Note: The &quot;Offset&quot; above is used to assign the Address for &quot;Lastcell&quot; to the 8th column to the right of Column A, which is column I. You would of course adjust this number to match with the number of columns in your database.

While you might be familiar with assigning range names, I feel I should list the steps, for the benefit of others who might read this and are not familiar. While there are different ways to assign a range name, the method I always recommend is the following:

a) Highlight the cell or range-of-cells
b) Hold down the <Control> key and hit <F3>
c) Type the name
d) Hit <Enter>

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Zathras]

Allow me to throw in this variation (if for &quot;best&quot; one reads &quot;shortest&quot:

  Sub CountRows()
    MsgBox ActiveSheet.UsedRange.Rows.Count
  End Sub

There are anecdotal reports of UsedRange not working correctly, but I believe that that is due to a mis-understanding of UsedRange. As long as there are data in row 1, UsedRange.Rows.Count is the same as the last row number. The problem arises when row 1 is empty (or the first n rows are empty). In that case UsedRange.Rows.Count still correctly reports the number of rows in the used range. It's just that when the first n rows are unused, the last used row number would be UsedRange.Rows.Count + n.

In your case, since row 1 is not blank. UsedRange.Rows.Count should work ok.

 

[RobBroekhuis]

The problem with usedrange arises when the &quot;last&quot; row contains formatting without content. For example, if I have data in the first 7 rows, but make a cell in row 12 bold, then activesheet.usedrange.rows.count is 12. Most users are only interested in the rows with content, in which case the end(xldown) or end(xlup) approaches are more reliable. If worksheet data is manipulated ONLY programmatically, so that there is little chance of stray formatting, then both should work equivalently.
Rob

 

[Zathras]

Rob, thanks for the clarification. You are absolutely correct (of course). Interesting that if the entire column is formatted, there's no problem, but formatting individual cells does indeed lead to a possible invalid row count with UsedRange. (Excel 2000)

Under the category &quot;sauce for the gander&quot; it should be mentioned that interrogating a single column may also lead to an incorrect conclusion, if the last row is blank in that column. (I.e., Cell A1000 may be blank while cell B1000 may have data.) Perhaps, the routine should be extended to include an end/right to verify that you end up in column IV? (Although one could construct a case where B1000 thru IV1000 are blank and B1001 has data.) Can you come up with a truly &quot;bullet proof&quot; method to find the last non-blank row?

 

[Loomah]

There are also (at least) two FAQs in this forum thet give other possible solutions fo finding last/first row or column containing data. Self promotion I know but......

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Just to add to this - the rows do not even have to have formatting

If there was data in the cell and it has been cleared or even if the cell been deleted - until the workbook is re-saved, the usedrange will include those cells.

Plus - in this instance - the data starts from row 4 so you'd need to take off a constant of 4 from any usedrange calc

If you want a &quot;bulletproof&quot; way of doing it - it'll take a bit longer

If your data starts in row 1 then I'd suggest using &quot;usedrange&quot; but reset it 1st

myReset = activesheet.usedrange.rows.count
lRow = activesheet.usedrange.rows.count



Other than that, this might do it:
lRow = 0
for i = 1 to range(&quot;IV1&quot.end(xltoleft).column
tempRow = cells(65536,i).end(xlup).row
if temprow > lRow then
lRow = temprow
maxCol = i
else
end if
next i
msgbox &quot;Last row is: &quot; & lRow & &quot; in column: &quot; & maxCol Rgds
Geoff

Estne volumen in toga, an solum tibi libet me videre?

 

[RobBroekhuis]

Geoff, most of my worksheets have just one cell filled in the first row - A1, with a title of some sort. So your example would only check column 1. I think in this case, using usedrange.rows.count+usedrange.row-1 is a properly conservative approach to the number of columns to check. Even if usedrange isn't set properly, it will always be set larger than necessary.
Rob

 

[xlbo]

Very true Rob - although I only used 1 'cos that's where I put my headers

That can obviously be changed to accomodate headers in different rows and I wasn't trying to make a universal solution as there simply isn't one.

Essentially, the layout of the data will determine what the best way to define the last row is.

99% of the time I can use range(&quot;A65536&quot.End(xlup) 'cos I try an make sure that my data is in database style setup - ie there is a key on the far left and any row with ANY data will at least have the key (if not much else).

Rgds
Geoff

Estne volumen in toga, an solum tibi libet me videre?

 

[RobBroekhuis]

I agree, Geoff - same goes for me. But your more elaborate approach above is foolproof, as long as you use usedrange to determine the number of columns to check.
Rob

 

[Loomah]

With this it doesn't matter what shape your data is, square, triangular, round or random or if there are gaps, empty rows, empty columns or formatting or anything - it doesn't care and gives a true result.

Sub mit()
Dim lRow As Long
Dim iCol As Integer
lRow = ActiveSheet.Cells.Find(what:="*", _
    searchdirection:=xlPrevious, searchorder:=xlByRows).Row
iCol = ActiveSheet.Cells.Find(what:="*", _
    searchdirection:=xlPrevious, searchorder:=xlByColumns).Column
MsgBox "Last used Column is " & iCol & " & Last used Row is " & lRow

lRow = ActiveSheet.Cells.Find(what:="*", _
    searchdirection:=xlNext, searchorder:=xlByRows).Row
iCol = ActiveSheet.Cells.Find(what:="*", _
    searchdirection:=xlNext, searchorder:=xlByColumns).Column
MsgBox "First used Column is " & iCol & " & First used Row is " & lRow

End Sub

;-) If a man says something and there are no women there to hear him, is he still wrong?