Number of rows in xls file

G

Gerry Metze

I'm trying to automate processing of an .xls file. Are
there variables/parameters/whatever I can read that tell
me how many rows and columns there are in the .xls file?
The file has several blank lines interspersed, so looking
for blank lines isn't enough.
 
R

Ron de Bruin

Hi Gerry

Am I correct that you want to know how many rows have data on you sheet?
Or do you want to know the last used row/column on your sheet
 
G

Guest

Hi Ron --

Yes; I can't look for empty rows to stop a reading loop
because there are embedded empty rows in the file.

I want to read the data into a VB array for further
processing.

Thanks,
Gerry

-----Original Message-----
Hi Gerry

Am I correct that you want to know how many rows have data on you sheet?
Or do you want to know the last used row/column on your sheet

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"Gerry Metze" <[email protected]>
Click to expand...
wrote in message [email protected]...
 
R

Ron de Bruin

I hope I understand you correct(my bad english)

You can check in the loop if a row is empty

With ActiveSheet
'code
If Application.CountA(.Rows(Lrow)) = 0 Then .......
'Lrow is the rownumber in the loop
'code
End With
 
G

Guest

No problem with English.
I think I can figure out how to stop the loop from the
examples on your website.

Thanks,
Gerry Metze
 
B

bill

Another way to go only to the last row in a file is to use
the special cells command for example

Sub Active_row()
Selection.SpecialCells(xlCellTypeLastCell).Select
r = Selection.Row
For i = 1 To r
Cells(i, 1) = "ACTIVE"
Next i
End Sub

will put ACTIVE into the first column for every row above
and including the last active row in the worksheet
whenever the sub Active_row is called.
 
G

Gerry Metze

Thanks a lot, Bill! It works like a charm. I also used
c = Selection.Column to read just the cells that were
filled. A lot better than filling a 65536x256 array with
mostly empty cells.
 
Top