Select rows with data

I am guessing the rows of data don't have empty rows mixed in with them. Is this correct? If so then you can find the last row by using

Sub GetLastRow()
Dim rng as Range

Set rng = Activesheet.Range("A1").end(xlDown)

then apply that to your Pivot Code. Probably easier would be to

set rng = Activesheet.UsedRange

This will grab all the data on the sheet.

HTH

I can't get it to work the way I normally do this and don't have any references around me but I found a way to work around it. To be honest I don't like what I did but it works.

Sub Macro1()
Dim cell As Range
Dim txt as String

Set cell = Worksheets("Data").Range("A1:" & ActiveSheet.Range("A1").End(xlDown).End(xlToRight).Address)
txt = "Data!" & Application.ConvertFormula(cell.Address, xlA1, xlR1C1)

Set cell = ActiveSheet.Range("A1:" & ActiveSheet.Range("A1").End(xlDown).End(xlToRight).Address)
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, SourceData:= _
        Array(txt, "Item1")).CreatePivotTable TableDestination:="", _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1)
    ActiveSheet.Cells(1, 1).Select
    ActiveSheet.PivotTables("PivotTable1").ColumnGrand = False
    ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems("Sum of Value" _
        ).Position = 1
    Range("A5").Select
    Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        False, True, False, False)
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
    
End Sub

Couldn't use the .UsedRange function because of the "Keyboard Shortcut: Ctrl+a" you have on the Data sheet so i used what you see above.