I need help making the code in Macro2 (see below) work on the active table which can start on A1 or another row. What I'm attempting to do is run the DeleteHiddenRows9 macro, move to the first column, sort the table based on data in the first column (smallest to largest), find the last cell with data, delete all rows in the table that don't have data, then go back to the header row in the first column. My company is still on Excel 2013 . Thank you!
-------------------------------------
Sub DeleteHiddenRows9()
Dim r As Range, q As Range
Set q = ActiveSheet.UsedRange.Columns("A").Offset(1)
Set r = q.SpecialCells(xlCellTypeVisible)
Range(ActiveCell.Address).AutoFilter
r.EntireRow.Hidden = True
q.SpecialCells(xlCellTypeVisible).EntireRow.ClearContents
q.EntireRow.Hidden = False
End Sub
--------------------------------
Sub Macro2()
Selection.End(xlToLeft).Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Open - DupeReview").ListObjects("Table_DupeReview"). _
Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Open - DupeReview").ListObjects("Table_DupeReview"). _
Sort.SortFields.Add Key:=ActiveCell.Offset(-1, 0).Range("A1:A2782"), SortOn _
:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Open - DupeReview").ListObjects( _
"Table_DupeReview").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.End(xlUp).Select
End Sub
-------------------------------------
Sub DeleteHiddenRows9()
Dim r As Range, q As Range
Set q = ActiveSheet.UsedRange.Columns("A").Offset(1)
Set r = q.SpecialCells(xlCellTypeVisible)
Range(ActiveCell.Address).AutoFilter
r.EntireRow.Hidden = True
q.SpecialCells(xlCellTypeVisible).EntireRow.ClearContents
q.EntireRow.Hidden = False
End Sub
--------------------------------
Sub Macro2()
Selection.End(xlToLeft).Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Open - DupeReview").ListObjects("Table_DupeReview"). _
Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Open - DupeReview").ListObjects("Table_DupeReview"). _
Sort.SortFields.Add Key:=ActiveCell.Offset(-1, 0).Range("A1:A2782"), SortOn _
:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Open - DupeReview").ListObjects( _
"Table_DupeReview").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.End(xlUp).Select
End Sub