CODEWITHSUNDEEP
vbaexcel
Jose Cortez
Jose Cortez

Reputation: 93

End IF without block IF compile error

I am trying to run this macro to shift data up on multiple sheets after the rows have been deleted. I keep getting a compile error

End if Without Block If

Here is my VBA code:

Sub shiftmeup()
    Dim ws As Worksheet
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("contactunder") '/// The underhood of my contacts
    Set ws1 = wb.Sheets("Deposits")
    Set ws2 = wb.Sheets("Lending")
    Set ws3 = wb.Sheets("Client Notes")

    With ws.Range("D11:BJ392")
        For i = .Rows.Count To 1 Step -1
            If IsEmpty(.Cells(i, 1)) Then .Rows(i).Delete Shift:=xlUp
        Next

    With ws1.Range("E11:l392")
        For i = .Rows.Count To 1 Step -1
            If IsEmpty(.Cells(i, 1)) Then .Rows(i).Delete Shift:=xlUp
        Next


    With ws2.Range("E11:Y392")
        For i = .Rows.Count To 1 Step -1
            If IsEmpty(.Cells(i, 1)) Then .Rows(i).Delete Shift:=xlUp
        Next

    With ws3.Range("E11:E392")
        For i = .Rows.Count To 1 Step -1
            If IsEmpty(.Cells(i, 1)) Then .Rows(i).Delete Shift:=xlUp

            End If
        Next
    End With
End Sub

Upvotes: 0

Views: 6441

Answers (2)

DisplayName
DisplayName

Reputation: 13386

@Peh already told you about your issue

Here's a possible refactoring of your code

first, assign a repetitive task to a specific routine:

Sub DeleteSheetRows(rng As Range)
    If WorksheetFunction.CountBlank(rng) > 0 Then rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

then call that specific routine from your "main" code:

Sub shiftmeup()
    Dim ws As Worksheet
    Dim sheetNames As Variant, rangeAddresses As Variant
    Dim i As Long

    sheetNames = Array("contactunder", "Deposits", "Lending", "Client Notes")
    rangeAddresses = Array("D11:BJ392", "E11:l392", "E11:Y392", "E11:E392")

    With ThisWorkbook ' reference desired workbook
        For i = 1 To UBound(sheetNames) ' loop through sheet names
            DeleteSheetRows .Worksheets(sheetNames(i)).Range(rangeAddresses(i)) ' call rows deleting routin passing current worksheet proper range
        Next
    End With
End Sub

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

Always format and indent your code correctly, otherwise you don't see your issues (I did that for you in your question).

Now you see that the first 3 With have no End With. Every With needs its own End With!

Also there is a End If that is too much because all your If statements are 1-liners and then doen't need a End If.

There are 2 types of If statements:

  1. 1-liners If … Then … Else

    If IsEmpty(.Cells(i, 1)) Then .Rows(i).Delete Shift:=xlUp

    Note that in 1-line-statements no End If is allowed.

  2. Multi liners

    If IsEmpty(.Cells(i, 1)) Then 
    .Rows(i).Delete Shift:=xlUp
End If

You cannot mix them.

Additionally I recommend to use descriptive variable names instead of ws1, ws2, … Which makes your code much more readable and better maintainable.

Upvotes: 2

Related Questions