Error pivot table with all rows populated

[happydonut]

Hi,

I have a macro to create pivot table, however im getting the error "This command requires at least two rows of source data." when trying to run it when all rows are populated. Here is my code:

Sub Macro1()
'
' Macro1 Macro
'

'
    Dim Last_Row As Long
    Last_Row = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Sheets.Add.Name = "Results"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R9C1:R" & Last_Row & "C7", Version:=6).CreatePivotTable TableDestination:= _
        "Results!R3C1", TableName:="PivotTable1", DefaultVersion:=6
    Sheets("Results").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Amount IN"), "Sum of Amount IN", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Amount OUT"), "Sum of Amount OUT", xlSum
End Sub

These lines gets marked in yellow:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R9C1:R" & Last_Row & "C7", Version:=6).CreatePivotTable TableDestination:= _
"Results!R3C1", TableName:="PivotTable1", DefaultVersion:=6


Any suggestions how to fix it?
Appreciated

 

[Trevor_S]

I think that Last_Row may be set as the top row, due to the code End(xlUp)

Try putting the following line immediately after the line that starts Last_Row... Then run the macro. It should display a message box with the last row of data, but I suspect it will actually show the first row.

MsgBox(Last_Row)

 

[happydonut]

I get the number 9, then the error..

 

[happydonut]

I changed the End(xlUp) to End(xlDown).. seems to be working I guess.. is it correct?

 

[Trevor_S]

Yes that should be ok. The only thing I wasn't certain about is whether End(xlDown) would send it to the bottom of the spreadsheet (row 1,000,000+), rather than just the last row containing data. But if it's working, it must be ok!

 

[happydonut]

I removed the last 4 rows to test.. then it was End(xlUp) that worked.. so its either way?

 

[Trevor_S]

End(xlUp) and End(xlDown) work the same as pressing the End and Up (or End and Down) keys together. If the current cell is in the middle of a block of data, it will go to the last cell of that block, in whichever direction. But if the current cell is at the end of a block of data, it will go up (or down) until it finds the next block.

So exactly what happens depends on your starting point and what gaps there may be. But xlDown is better for your purposes than xlUp. xlUp will only work if the starting point is at least a couple of rows below the end of the data. Otherwise, by looking upwards, it will miss some or all of your data.

 

[happydonut]

Yes that should be ok. The only thing I wasn't certain about is whether End(xlDown) would send it to the bottom of the spreadsheet (row 1,000,000+), rather than just the last row containing data. But if it's working, it must be ok!

It's true that xlDown send it to the bottom (even when all possible rows in the Excel sheet are not populated, which is also why I get a (blank) in my Pivottable (not preferable)). So, is it really better with xlDown rather than xlUp? The way I see it, xlDown is only good when all rows are populated. Worth to mention, there is no gap between rows in my data, for instance if the Pivot is created on cells A1 down to A10, all cells populated, with no blank between them.