Question Used rows in an excel sheet (1 Viewer)

[Super Suarez]

Hi All,

I have the following code which returns me the number of rows in an excel sheet:-

Lastrows = .Worksheets(1).Cells.Find(What:="*", SearchDirection:=2, SearchOrder:=1).Row

How do I mod the code to find the last 'used' row in the sheet?

The sheet is automatically generated daily, so I can't change anything in that area. It's always 2000 rows long and usually only has around 1100-1200 rows of data.

It's just that I do a for loop later on in my code for 1 to Lastrows and would like it as exact as I can.

Hope you can help.

 

[David R]

How do you know a row has been 'used'? Does it have additional columns of data?

Just modify your Find() to look for one of those pieces of data instead.

 

[Super Suarez]

If a row has been used it will have data in it. Quite what data I'm not sure, but it will not be empty.

I've been trawling the net for an answer. All a bit confusing

 

[David R]

It's just a matter of the right search terms (isn't it always?).

http://stackoverflow.com/questions/6301665/row-number-of-last-cell-with-data seems useful to you.

 

[Mihail]

"In fly"

Function LastUsedRow As Long
Const MaxRowsInExcel As Long = 65536
Const MaxColumnsInExcel As Long = 256

Const FirstRWithData = 1
Const FirstColumnWithData = 1

  For R = 1 to MaxRowsInExcel 
     For C = 1 to MaxColumnsInExcel 
       If not IsEmpty(Cells(R,C) Then
         GoTo NotEmptyRow
       End If
    Next C
    LastUsedRow = R
Exit Function

NotEmptyRow:
  Next R

  MsgBox("The Worksheet is Full")
End Function