Andrew
Andrew

Reputation: 11

VBA code to Sort, Identify the last row and Clear contents

I am trying to write a macro to do the following:

  1. Sort by Column
  2. Clear the contents of rows that do not contain "* Total" in Column C

The macro works if I hard code a range value for the last row of the spreadsheet, but since this row may change I want the macro to identify the last row and work back from there.

Sub SortandDelete()
    Application.ScreenUpdating = False
    ActiveSheet.AutoFilterMode = False
    CC = Cells(Rows.Count, "C").End(xlUp).Row
    Columns("A:AW").Sort key1:=Range("C1" & CC), _
       order1:=xlAscending, Header:=xlYes

    For MY_ROWS = Range("CC").End(xlUp).Row To 1 Step -1
        If Range("C" & MY_ROWS).Value <> "*   Total:" Then
            Rows(MY_ROWS).ClearContents
        End If
    Next MY_ROWS

 End Sub

Upvotes: 1

Views: 1293

Answers (1)

user1016274
user1016274

Reputation: 4209

Please compare this to your code to see the coding errors:

Sub SortandDelete()
    Dim lastrow As Long
    Dim row As Long
    Dim r As Range

    Application.ScreenUpdating = False
    ActiveSheet.AutoFilterMode = False

    lastrow = Cells(Rows.Count, "C").End(xlUp).row
    Columns("A:AW").Sort key1:=Range("C1:C" & lastrow), _
       order1:=xlAscending, Header:=xlYes

    For row = lastrow To 1 Step -1
        Set r = Range("C" & row)
        If r.Value <> "*   Total:" Then
            r.EntireRow.ClearContents
        End If
    Next row
    Application.ScreenUpdating = True
End Sub

You could make life easier for you if you'd use meaningful names for variables, and define them before use. Here, CC was a row number but later in the code you used it as a string (denoting a column "CC"). Then, you combined CC with a "C1" which certainly denotes a different row number than intended.
As you are only clearing the rows and not deleting them you could scan the range from top to bottom as well - the row number never changes by clearing the contents. But later you might be tempted to delete the rows entirely and then you'd need to do that from bottom up.

Upvotes: 2

Related Questions