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

Need a little help, I have a Macro that automatically creates the pivot table and chart, it's all working fine But I am getting (empty) in my urine table because my range is done all the way up to 65536. How do I get myself past / column in my source data, so I do not get any free space. Data is constantly changing, so it should be automatically

Here is the source data, I want to get the R65536C37 to automatically be generated based on the last column of the "raw" sheet.

>

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

I've tried;

  LastRow = ActiveSheet.UsedRange.Rows.Count SourceData: = "! Raw R1C1:" & amp; Last Line & amp; Select Season ("A7"). Choose ActiveWorkbook PivotCaches.Add (SourceType: = xlDatabase, SourceData: = _. "Raw R1C1: R65536C37") CreatePivotTable _ TableDestination: = "! Frontpage R7C1", TableName: = "PivotTable2", _ DefaultVersion :. = XlPivotTableVersion10 Sheet (select "Frontpage") cells (7, 1) .Select ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source: "! Frontpage $ A $ 7: $ H $ 22" = range (.) ActiveChart .ChartType = xlColumnClustered ActiveSheet.PivotTables ( "PivotTable2") with PivotFields ( "priority") .Orientation = xlRowField .position ActiveSheet.PivotTables (with = 1 end "PivotTable2"). AddDataField ActiveSheet.PivotTables (_ "PivotTable2"). PivotFields ("Case ID"), "Calculation of Case ID", xlCount ActiveChart. Parent.Name = "IncidentsbyPriority" ActiveChart.ChartTitle.Text = "as the Events" dim RngToCover preferred range as low Chtob ChartObject set RngToCover = ActiveSheet.Range ( "D7: L16") set Chtob = Act IveSheet.ChartObjects ( "Incidentsby Prioryity") ChtOb.Height = RngToCover.Height Change size ChtOb.Width = Change RngToCover.Width size ChtOb.Top = RngToCover.Top 'reposition CHTOb.Left = RngToCover.Left' reposition  < / Pre> 
 Any help would be greatly appreciated, I need to repeat it in four other pivotions so that I can avoid avoiding my table and charts. For example, with a formula, create a named range named RawData, for example: 
 
 

"post-text" itemprop = "text">

/> = OFFSET (Raw! $ A $ 1,0,0, COUNTA (Raw! $ A: $ A), COUNTA (Raw! $ 1: $ 1))

There is no problem with 1 or line 1 (empty space / empty space from the end of the data).

P> If there are spaces in your data, then you need to use a different function: You can do this from