Conditional If then structure VBA

[WindsorKnot]

Hi,

I'm having trouble with my code below.

Basically I want the code to determine if the cell before and after the active cell contains a value and if both these conditions are true to delete the active row.

I keep getting a runtime error 1004 when I try this.

Thanks!

Sub rowdelete()
    Dim i As Integer
    Sheet4.Activate
    For i = 1 To 30000
        If Cells(i, 1).Value = IsEmpty(ActiveCell) Then
            If Cells(i, 1).Offset(-1, 0).Value = "" _
            And Cells(i, 1).Offset(1, 0).Value = "" Then
                Cells(i, 1).Select
                Selection.Delete Shift:=xlUp
            End If
        End If
    Next i
End Sub

 

[Scott Huish]

There's no need to select and if you are using a For Next Loop to delete you want to work backward through the rows not forward. Also you can't check an offset of above Row 1 because there is no such row there, no A0.

Your description says you want to delete the row if the row before it and the row after it have a value but your code checks for blank, which is it?

 

[WindsorKnot]

There's no need to select and if you are using a For Next Loop to delete you want to work backward through the rows not forward. Also you can't check an offset of above Row 1 because there is no such row there, no A0.

Your description says you want to delete the row if the row before it and the row after it have a value but your code checks for blank, which is it?

If the cell before and after have a value and active cell is blank

 

[Blade Hunter]

You should take HOTPEPPERS advice and not select for starters.

Your error in your existing code is here:

Cells(i, 1).Value = IsEmpty(ActiveCell)

I believe it should be:

IsEmpty(Cells(i,1))

If you need help implementing HOTPEPPERS suggestion post back.

Cheers

Dan

 

[WindsorKnot]

its been a late night. This is current code same runtime error 1004

Sub rowdelete()
Dim i As Integer
Sheet4.Activate
For i = 1 To 30000
If IsEmpty(Cells(i, 1)) Then
If Not IsEmpty(Cells(i - 1, 1)) _
And Not IsEmpty(Cells(i + 1, 1)) Then
Rows(i, 1).Delete
End If
End If
Next i
End Sub

 

[Scott Huish]

Do you actually have 30,000 rows of data?

Does this work for you?

Sub test()
Dim i As Long
For i = 30000 To 2 Step -1
    If Cells(i - 1, 1) = "" And Cells(i + 1, 1) = "" Then Rows(i).Delete
Next
End Sub

 

[WindsorKnot]

Do you actually have 30,000 rows of data?

Does this work for you?

Sub test()
Dim i As Long
For i = 30000 To 2 Step -1
    If Cells(i - 1, 1) = "" And Cells(i + 1, 1) = "" Then Rows(i).Delete
Next
End Sub

HotPepper you truely are an MVP this works perfectly. Yes I really do have 30,000 rows of data. Its from an incurred/paid lag triangle report

 

[Blade Hunter]

Just a note, if you wanted to do this manually instead of with code, Put a number next to the rows (Put in 1 then 2 then highlight and double click the bottom right corner of the selection)

Sort by any column where the blanks appear

Delete the blank rows

Resort by your row numbers you entered earlier.

Done

Should take about 15 seconds to do this.

Cheers

Dan

 

[WindsorKnot]

HotPepper, the cat just ate my toungue. I've noticed that it skipped over some rows and the loop doesn't end until I force it to end any follow ups?

 

[WindsorKnot]

I think the issue might be that these empty rows are merged with the row that has a value