Determining End Of data
 

I have a query that brings in data. Sometime 10 rows, sometimes 50.

I have a macro that creates a pivot table of the returned data.

If I code the pivot for 10 rows of data, the pivot will be incorrect when
anything other than 10 rows of data is returned.

How do I determine what the last row of data is OR the number of rows of
data? Is there a funtion that can look at each row and tell me what the
first empty/blank row is?

Thanks in advance
--
bnhcomputing

Determining End Of data
 

To determine the last row of data you are best off to go to the last row in
the spreadsheet (65,536) and then move up from there until a non blank cell
is found something like this

msgbox sheet1.cells(rows.count, "A").end(xlup).row
--
HTH...

Jim Thomlinson


"bnhcomputing" wrote:

I have a query that brings in data. Sometime 10 rows, sometimes 50.

I have a macro that creates a pivot table of the returned data.

If I code the pivot for 10 rows of data, the pivot will be incorrect when
anything other than 10 rows of data is returned.

How do I determine what the last row of data is OR the number of rows of
data? Is there a funtion that can look at each row and tell me what the
first empty/blank row is?

Thanks in advance
--
bnhcomputing

Determining End Of data
 

Hi BNH,

Perhaps this will help:

Sub Tester()
Dim LastRow As Long
Dim LastCol As Long
Const col As String = "A" '<<=== CHANGE
Const RW As Long = 2 '<<=== CHANGE


LastRow = Cells(Rows.Count, col).End(xlUp).Row

LastCol = Cells(RW, Columns.Count).End(xlToLeft).Column

Debug.Print LastCol, LastRow

End Sub

---
Regards,
Norman



"bnhcomputing" wrote in message
...
I have a query that brings in data. Sometime 10 rows, sometimes 50.

I have a macro that creates a pivot table of the returned data.

If I code the pivot for 10 rows of data, the pivot will be incorrect when
anything other than 10 rows of data is returned.

How do I determine what the last row of data is OR the number of rows of
data? Is there a funtion that can look at each row and tell me what the
first empty/blank row is?

Thanks in advance
--
bnhcomputing