keep getting 'else without if' error in excel vba-CodePudding

I keep getting else without if error even though it's pretty simple code so not sure what I'm missing.

The aim is to just sort through column W from top to bottom, if it is not empty then it needs to be set to empty, if it is already empty then move on to the next row. This is what my code is:

Sub emptycolumn()

Dim i as integer
Dim last_row as Integer

'find the last row number
last_row = Cells(Row.Count, 1).End(xlUp).Row 

    
For i = 2 To last_row
    If Range("w" & i).Value <> "" Then Range("w" & i).Value = ""
    Else:
    End If
Next i
End Sub

CodePudding user response：

End If is only required at the end of a block of code. Because your Range("w" & i).Value = "" is on the If line, there is no need for the End If.

Either of these would work instead of your code:

For i = 2 To last_row
    If Range("w" & i).Value <> "" Then Range("w" & i).Value = ""
Next i

Or..

For i = 2 To last_row
    If Range("w" & i).Value <> "" Then
        Range("w" & i).Value = ""
    Else
        'do something else
    End If
Next i

Having said all that, if that is the full extent of your requirement for that loop - you could replace the entire loop with:

Range("W2:W" & last_row).Value = ""

.. as it appears that all you're doing is changing any cells that are not "" to "". Why not just change all the cells to "" regardless - there is very little difference unless you're relying on an Change Event to do something else.