Range that selects all cells with data in a sheet or column

[mloveloc]

Hi new to VBA and I am having trouble changing my range to select all cells with data. I originally was changing the range to include all possible cells however, this is not the most efficient way. I have tried using last row and a few other formulas but to no avail. I have two scenarios, one to select the whole sheet and other instances where I need to select all in a particular column. Any help would be much appreciated! Here is what I have so far!

Sub Redemptions()


ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:U" & LastRow), , xlYes).Name = _
"Table3"
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table3").Sort.SortFields.Add _
Key:=Range("C2:C65536"), 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:J65536")
Range("J2:J65536").Select

ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table3").Sort.SortFields.Add _
Key:=Range("O1:O65536"), 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:P65536").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

 

[JLGWhiz]

If you do not know the extent of a vertical range in a column of data and want to capture all but the header row in a variable, the assuming the header is in only row 1 and the column is F.

Dim rng As Range
With ActiveSheet
	Set rng = .Range("F2", .Cells(Rows.Count, "F").End(xlUp))
	rng.Copy Sheets(2).Range("A2") 'as an example
	'Code to do other stuff here
End With

Of if you have variable length columns and want to make sure you get the last row of data in all of them in columns B:F.

Dim rng As Range, lr As Lont, sh As Worksheet
Set sh = ActiveSheet
sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row 'Finds last cell with data on worksheet
Set rng = sh.Range("B2:F" & lr)
rng.copy Sheets(2).Range("A2") 'As and example
'Code to do other stuff

the point being that the last row of data may not be the same in all columns, so using the last row for the worksheet data would ensure that all rows in all columns would be covered. Another thing to be aware of is when using these methods as a preset variable value, if you add or delete rows with a subsequent part of your code, you might need to re-initialize your range parameters to accomodate the new last row value. The variable parameters do not change automatically with changes on your worksheet while the macro is running, but will change the next time the macro runs and initializes the applicable variable.