Range question

[mloveloc]

Hello, I am new to VBA and was wondering how I change my range to go to the end of the data. I have reports that vary in row number everyday. At the moment I have it set at 200, but I would like it to just go to the end of the data. Here is what I have so far:

Sub Macro3()
'
' Macro3 Macro
'

'
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$U$200"), , xlYes).Name = _
"Table1"
Range("I1").Select
Selection.EntireColumn.Insert
ActiveCell.FormulaR1C1 = "REP"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],"" "",RC[-1],"", "",RC[-4])"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I200")
Range("I2:I200").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
Range("N1").Select
Selection.EntireColumn.Insert
ActiveCell.FormulaR1C1 = "FUND"
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'FundSERV reference codes for purchases.xlsx'!Table1[#All],2,FALSE)"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N200")
Range("N2:N200").Select
Columns("Q:R").Select
Selection.Style = "Comma"
Range("O3").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=xlPivotTableVersion10).CreatePivotTable TableDestination _
:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion10
Sheets("Sheet2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("REP")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("FUND")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("GROSS_AMT"), "Sum of GROSS_AMT", xlSum
Range("B5:C7").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Range("C5:C7").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("REP").AutoSort xlDescending _
, "Sum of GROSS_AMT", ActiveSheet.PivotTables("PivotTable1").PivotColumnAxis. _
PivotLines(2), 1
Range("B6").Select
ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight3"
ActiveSheet.PivotTables("PivotTable1").ShowTableStyleRowStripes = True
Range("A1").Select
ActiveCell.FormulaR1C1 = "Purchases"
Range("A1").Select
Selection.Font.Bold = True
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Data"
Range("B36").Select
End Sub

Thanks!

 

[lrobbo314]

If you're talking about range I2:I and whatever the last row is then it would be

Range("I2",Range("I"&Rows.count).end(xlup))

 

[mloveloc]

Hmm, I tried that. I think I am more looking to change the range for ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$U$200"), , xlYes).Name = _"Table1" and for any subsequent ranges that follow.
My issue is that when I run the macro the way it is now I have a bunch of blanks in the table/ I want to table to end when the data ends so that it wont create any blanks.

 

[lrobbo314]

How about this?

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1", Range("U" & ActiveSheet.UsedRange.Rows.Count)), , xlYes).Name = "Table1"