Automaically select the lastrow in PivotTable SourceData to avoid (blanks)

Question

Hi A little help needed, I have a Macro automatically creating pivot tables and charts, this is all working fine but I am getting (blank) in my pivot table becuase my range is all the way to 65536. How do I automatically get the lastrow / column in my source data so I dont get any blanks. The data is changing constantly so this needs to be automatic

Here is the source data, I am looking to get the R65536C37 to be automatically generated based on the lastcolumn of the "raw" sheet

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "raw!R1C1:R65536C37").CreatePivotTable _
    TableDestination:="Frontpage!R7C1", TableName:="PivotTable2", _
    DefaultVersion:=xlPivotTableVersion10

I have tried;

LastRow = ActiveSheet.UsedRange.Rows.Count
SourceData:= "raw!R1C1:" & LastRow & C37"

Pivot Macro

    Sheets("Frontpage").Select
Range("A7").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "raw!R1C1:R65536C37").CreatePivotTable _
    TableDestination:="Frontpage!R7C1", TableName:="PivotTable2", _
    DefaultVersion:=xlPivotTableVersion10
Sheets("Frontpage").Select
Cells(7, 1).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("Frontpage!$A$7:$H$22")
ActiveChart.ChartType = xlColumnClustered
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Priority")
    .Orientation = xlRowField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
    "PivotTable2").PivotFields("Case ID"), "Count of Case ID", xlCount
ActiveChart.Parent.Name = "IncidentsbyPriority"
ActiveChart.ChartTitle.Text = "Incidents by Priority"
Dim RngToCover As Range
Dim ChtOb As ChartObject
Set RngToCover = ActiveSheet.Range("D7:L16")
Set ChtOb = ActiveSheet.ChartObjects("IncidentsbyPriority")
ChtOb.Height = RngToCover.Height ' resize
ChtOb.Width = RngToCover.Width ' resize
ChtOb.Top = RngToCover.Top ' reposition
ChtOb.Left = RngToCover.Left ' reposition

Any help would be greatly appreciated. I need to repeat this in four other pivots so as to avoid getting (blank) in my tables and charts.

Answer 1

I usually use a Named Dynamic Range. For example create a Named Range called RawData with a formula :
=OFFSET(Raw!$A$1,0,0,COUNTA(Raw!$A:$A),COUNTA(Raw!$1:$1))

This assumes that there are no blanks contained within the data in column 1 or row 1 (blanks/empty beyond the end of data is not a problem).

If there are blanks within your data then you need to use a different function: you can download a set of functions (fxlCountFuncs.zip) for this kind of counting from
http://www.decisionmodels.com/downloads.htm