Count and store number of rows

[Dave Coz]

Hi all.
I have a flat file that I take in from a vendor. based upon the days
business the 'transactions" which are in rows can be from zero (no activity)
to say 1,000 rows. I have a macro that takes the flat file and breaks into
excel rows and columns. The problem is that I do not count the rows before I
start the macro and I use "select.end(xldown)' statements to manage the data.
This does not work when there is only one (or none) row of data. Does anyone
know how to: count the rows and store that number so that I can react to
that? So if there are no transactions, I can stop the macro?
Appreciate any and all help.
Thanks
Dave

 

[Don Guillett]

lr=cells(rows.count,"a").end(xlup).row
if cells(lr,1)<yournumberhere then exit sub

 

[Ron Rosenfeld]

Hi all.
I have a flat file that I take in from a vendor. based upon the days
business the 'transactions" which are in rows can be from zero (no activity)
to say 1,000 rows. I have a macro that takes the flat file and breaks into
excel rows and columns. The problem is that I do not count the rows before I
start the macro and I use "select.end(xldown)' statements to manage the data.
This does not work when there is only one (or none) row of data. Does anyone
know how to: count the rows and store that number so that I can react to
that? So if there are no transactions, I can stop the macro?
Appreciate any and all help.
Thanks
Dave

If the data is contiguous (i.e. no blank rows), and rg is some cell within the
data (e.g. A1) then

rg.CurrentRegion.Rows.Count

However, whether A1 is blank or not, this will return a value of 1, so you may
need some special code to check when there is only one row.
--ron

 

[Shane Devenshire]

Hi,

It's hard to give answers without knowing what your code is and how the data
is laid out. But here is an idea

If ActiveCell <> "" Then 'Exits if if no data
If ActiveCell.Offset(1, 0) <> "" Then 'Data is selected if only one row
Range(ActiveCell, ActiveCell.End(xlDown)).Select 'Selects all data
if more than one row
End If
'Code to do what you want if data came in
End If

 

[Rick Rothstein]

Assuming you are checking Column A to determine the last row, something like
this should work...

With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If LastRow = 1 And .Range("A1") <> Then
'
' Put your code here... there is at least one row of data
'
Else
MsgBox "There is no data on this sheet!"
Exit Sub 'or Function
End If
End With