keep getting 'else without if' error in excel vba

Question

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

You do not need Else:, remove it.

Harun24hr
– Harun24hr

2022-07-20 08:22:54 +00:00
Commented Jul 20, 2022 at 8:22 — Harun24hr
– Harun24hr, Commented Jul 20, 2022 at 8:22

CLR · Accepted Answer · 2022-07-20 08:59:27Z

2

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.

edited Jul 20, 2022 at 8:59

answered Jul 20, 2022 at 8:28

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

keep getting 'else without if' error in excel vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related