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

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

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