Macros

First, determine which data range should be in your pivottable. For this, copy your data range in a new sheet and count the number of rows and columns :
dim dataCols as integer
dim dataRows as integer
dim sourceSheetName as string
Public Sub InitDataRange()

dim i as integer
sourceSheetName = ActiveSheet.Name
'count cols
i = 1
Do While Cells(1, i).Text <> ""
i = i + 1
Loop
dataCols = i - 1

'count rows
i = 1
Do While Cells(i, 1).Text <> ""
i = i + 1
Loop
dataRows = i - 1

End Sub

Then, select the sheet where you want to draw the pivottable and do it using dataRows and dataCols:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'" & sourceSheetName & "'!R1C1:R" & dataRows & "C" & dataCols).CreatePivotTable TableDestination:=Range("A1" _
), TableName:="my table name"

Once you've understood the basics, the possibilities are infinite !

How about using this for the SourceData ?

MyData = "'Sheet1'!" & Sheets("Sheet1").Range("A1").CurrentRegion.Address

This is faster than counting rows and columns... or you can use:

MyData = "'Sheet1'!" & Sheets("Sheet1").Range("A1",Range([A655536].End(xlUp),[IV1].End(xlToLeft))).Address

Juan Pablo 'count rows Do While Cells(i, 1).Text <> "" dataRows = i - 1 "'" & sourceSheetName & "'!R1C1:R" & dataRows & "C" & dataCols).CreatePivotTable TableDestination:=Range("A1" _ ), TableName:="my table name"

It's faster than counting rows and cols, BUT it retrieves a range with cells that have a FORMULA and even if the value is 0...So your pivottable is full of empty fields and zeros...

Any idea how to get the range with values<>0 in one line of code (without looping through the range) ?

It's an honnor to work with you, Senor Juan Pablo... How about using this for the SourceData ? MyData = "'Sheet1'!" & Sheets("Sheet1").Range("A1").CurrentRegion.Address This is faster than counting rows and columns... or you can use: MyData = "'Sheet1'!" & Sheets("Sheet1").Range("A1",Range([A655536].End(xlUp),[IV1].End(xlToLeft))).Address Juan Pablo 'count rows Do While Cells(i, 1).Text <> "" dataRows = i - 1 : End Sub : Then, select the sheet where you want to draw the pivottable and do it using dataRows and dataCols

Any idea how to get the range with values<>0 in one line of code (without looping through the range) ? It's an honnor to work with you, Senor Juan Pablo... : How about using this for the SourceData ? : MyData = "'Sheet1'!" & Sheets("Sheet1").Range("A1").CurrentRegion.Address : This is faster than counting rows and columns... or you can use

That depends. If you have a formula and you get a result o 0, then i would say it's an expected result, therefor, it should be included in the PivotTable (Maybe not for SUM, but AVERAGE and COUNT will care). Another thing, i don't trust very much the first one, it'll work well if the range doesn't have empty spaces, but it's too risky, so i like making sure, so that's why I always use the second one.

Using .End(xlUp) and .End(xlToLeft) provide the last col and row with data, and then i can just fin the VERY last cell by using

Dim LastCell as Range
Set LastCell = Cells([A655536].End(xlUp).Row,[IV1].End(xlToLeft).Column)

Now, if the source is "full of zeros and empty fields", well, that also depends on the integrity of your data list, and and expected results also. Now, if what you 're saying is to do a "prefiltering" of the list, in order to get a list "without zero's and empty fields" i would do first a Advanced Filter or even an AutoFilter, but then you would have to know WHICH field to filter...

Juan Pablo It's faster than counting rows and cols, BUT it retrieves a range with cells that have a FORMULA and even if the value is 0...So your pivottable is full of empty fields and zeros... Any idea how to get the range with values<>0 in one line of code (without looping through the range) ?

It's an honnor to work with you, Senor Juan Pablo...

With "full of empty fields" I meant that my sourceRange is linked to another sheet where I dump flat files that have a variable number of fields. So in my pivottable source I have to write the link formulas to my base data sheet. So that the .End method takes into account all fields even if they are empty.

For the rows, I wrote a sub that AutoFills to the right number of rows in the pivottable source sheet. It is quite long because I loop through the base data to find the number of rows. Maybe I should do the same for columns. I don't know if I make myself clear...Nevermind I just realized how simple it is to autofill by using the .End method.

Thank you Juan Pablo ! That depends. If you have a formula and you get a result o 0, then i would say it's an expected result, therefor, it should be included in the PivotTable (Maybe not for SUM, but AVERAGE and COUNT will care). Another thing, i don't trust very much the first one, it'll work well if the range doesn't have empty spaces, but it's too risky, so i like making sure, so that's why I always use the second one. Using .End(xlUp) and .End(xlToLeft) provide the last col and row with data, and then i can just fin the VERY last cell by using Dim LastCell as Range

'count rows Do While Cells(i, 1).Text <> "" dataRows = i - 1