Delete sorted rows

Question

I am subtotaling data, copying that data to another worksheet, then filtering for a column and deleting those rows.

The problem is that the range of the data to delete will vary every time the macro is run.

Here's an example of what I have:

Range("A2").Select
Columns("A:C").Select
Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(3), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("A2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveSheet.Range("$A$1:$C$396").AutoFilter Field:=2, Criteria1:="<>"
Rows("2:394").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$C$42").AutoFilter Field:=2
Range("A22").Select
Selection.End(xlDown).Select
Rows("42:42").Select
Selection.Delete Shift:=xlUp

A perfect time to read up on [How to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — BruceWayne, Feb 12 '19 at 23:46
`Select` and `Activate` are commands Excel requires for its user interface. They are *never* needed by VBA. Take this one step further. If you can't use `Select` then `Rows("42:42").Select : Selection.Delete` could be replaced with `Rows("42:42").Delete` (Correct assumption!). But if your code contains `Rows("42:42").Delete`, is it a surprise that row 42 gets deleted every time you run the code? If you remove all `Select` statements from your code it will be 60% smaller and much more comprehensible. You will be able to at least see the problems you may need to ask about. — Variatus, Feb 13 '19 at 00:36

score 0 · Accepted Answer · edited Feb 05 '21 at 00:18

So, instead of this:

"$A$1:$C$396"
"$A$1:$C$42"

Find the last used row and last used column dynamically.

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

'Ctrl + Shift + End
  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

And . . .

Dim sht As Worksheet
Dim LastColumn As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column

Delete sorted rows

1 Answers1