vba end if without block if

Question

I keep getting this error, but can't figure out why. It looks like all rules are followed.

The structure as I read is the following - If -> ElseIf -> End If. But here I get an error though it's all the same.

Sub hide()
        Application.ScreenUpdating = False
        Dim wRange As Range
        Set wRange = Range("A5:B10")

        Dim mergedRows As Integer
        Dim mergedColumns As Integer

        Dim cellFirst As Range

        For Each cell In wRange
            If IsEmpty(cell) Then
                cell.EntireRow.Hidden = True
            ElseIf cell.MergeCells Then
                mergeRows = cell.MergeArea.Rows.Count
                mergeColumns = cell.MergeArea.Columns.Count
                With cell.MergeArea
                    Set cellFirst = cell.MergeArea(Cells(1, 1))
                    If IsEmpty(cellFirst) Then
                        cellFirst.EntireRow.Hidden = True
                    End If
            End If
        Next
End Sub

type End With right before the last End If. In this case the error message is misleading, because the With block is the issue, not the If, but over time (and with good indenting practice, as you have done) you'll spot these much faster and know what to look for :) — Scott Holtzman
– Scott Holtzman, Commented May 23, 2017 at 13:22
@Scott Holtzman, Thanks! Didn't know With out to be finished too. You might answer, I'll accept. — Ans
– Ans, Commented May 23, 2017 at 13:24
you can actually remove the With cell.MergeArea as you never implement a proper With block. (nor do you need to). — Scott Holtzman
– Scott Holtzman, Commented May 23, 2017 at 13:26

JahKnows · Accepted Answer · 2017-05-23 13:38:17Z

2

You need to also close your With statement.

With cell.MergeArea
   Set cellFirst = cell.MergeArea(Cells(1, 1))
   If IsEmpty(cellFirst) Then
      cellFirst.EntireRow.Hidden = True
   End If
End With

answered May 23, 2017 at 13:38

JahKnows

2,7113 gold badges25 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

vba end if without block if

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