Reputation: 11
I am trying to write a macro to do the following:
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
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