Count and store number of rows

  • Thread starter Thread starter Dave Coz
  • Start date Start date
D

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
 
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
Click to expand...

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
 
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
 
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
 
Back
Top