Selecting entire sheet/ streamlining processes

[mloveloc]

Hi! I am new to creating macros and have fumbled my way through creating a macro that works, however, sometimes with a few errors. The first error I am continually experiencing is that if there is more data than I was expecting (over 300 rows) then I receive an error. To deal with this, I have been changing the number of rows as need be but I would rather have excel count how many rows are in the sheet. The quantity of rows is continually changing and has no theoretical maximum so I think this is the best option. I would also like any tips/tricks for better efficiency and overall good practice, as I am new to the world of macros. Any help advice or articles would be greatly appreciated! Here is what I have so far!

Sub Redemptions()

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$U$350"), , xlYes).Name = _
"Table3"
Range("A1:U350").Select
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table3").Sort.SortFields.Add _
Key:=Range("C2:C350"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table3").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Last = Cells(Rows.Count, "C").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "C").Value) = "TOTAL" Then
'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
Cells(i, "A").EntireRow.Delete
End If
Next i

Range("J1").Select
Selection.EntireColumn.Insert
ActiveCell.FormulaR1C1 = "REP"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],"" "",RC[-1],"", "",RC[-4])"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J350")
Range("J2:J350").Select

ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table3").Sort.SortFields.Add _
Key:=Range("O1:O350"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table3").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("P1").Select
Selection.EntireColumn.Insert
ActiveCell.FormulaR1C1 = "FUND"
Range("P2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'Z:\ManInvestments\Operations\Temp Working\Daily Purchases & Redemptions\[FundSERV reference codes.xlsx]Sheet1'!R1C1:R18C2,2,FALSE)"
Range("P2").Select
Selection.AutoFill Destination:=Range("P2:P350")
Range("P2:P350").Select

ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
Columns("W:W").Select
Selection.Style = "Comma"
Cells.Select
Range("N1").Activate
Selection.Columns.AutoFit

Columns("C:C").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveWindow.SmallScroll Down:=36
Selection.EntireRow.Delete
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table3", 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
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"TRADE_PLACED_ON_ FUND_SERV")
.Orientation = xlPageField
.Position = 1
End With
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("PivotTable1")
pt.AddDataField pt.PivotFields("GROSS_AMT"), "Sum of GROSS_AMT", xlSum
Range("B5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Range("E5:E59").Select
' pt.DataBodyRange.Columns.Count is the number of data columns
pt.PivotFields("REP").AutoSort 2, "Total Sum of GROSS_AMT", pt.DataBodyRange.Columns.Count, 1
Range("D17:D18").Select
pt.TableStyle2 = "PivotStyleLight2"
pt.ShowTableStyleRowStripes = True
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Redemptions"
Range("A1").Select
Selection.Font.Bold = True
ActiveWindow.SmallScroll Down:=30
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Data"
Sheets("Sheet2").Activate

Columns("D:D").Select
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("D1"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortTextAsNumbers
Range("D6").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=21
Range("D7").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("REP").AutoSort xlDescending _
, "Sum of GROSS_AMT", ActiveSheet.PivotTables("PivotTable1").PivotColumnAxis. _
PivotLines(3), 1
Range("C20").Select

End Sub

 

[revcanon]

You could define your last row by doing this:

Dim LastRow as Integer
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row  
' Choose a column that has data in the last row you are defining

Then you can use that variable for any range where you need it to go to that last row like this:

Range("J2:J" & LastRow)
Range("A2:A" & LastRow)
'etc...

Instead of calling the lastrow by it's number, you now call it by it's variable so it never cares what that number is.