Solved: Delete Rows makes Excel Hang

[MissRibena]

Hi everyone

I think this must be a very basic problem but I just can't figure out why this code won't work. I just want excel to delete any rows where Column A is blank. D is populated to the end of the data.



Sub DeleteRows()
Range("D1").Select
Do Until ActiveCell.Value = Empty
If ActiveCell.Offset(0, -3) = Empty Then
ActiveCell.EntireRow.Delete
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

I tried it this too but Excel still hangs:

Sub DeleteRows()
Range("D1").Select
Do Until ActiveCell.Value = Empty
If ActiveCell.Offset(0, -3) = Empty Then
ActiveCell.EntireRow.Delete xlUp
End If
Loop
End Sub


Has anyone any suggestions as to where I'm going wrong?

Thanks
Rebecca

[lucas]

Hi Rebecca,
You might try this.....use the commented line if you wish to use a named sheet:

Sub test222()
'Sheets("SomeSheet").UsedRange.SpecialCells(xlBlanks).EntireRow.Delete
ActiveSheet.UsedRange.SpecialCells(xlBlanks).EntireRow.Delete
End Sub

[MissRibena]

Thanks Lucas. That does the trick and much more neatly than the path I was trying to follow.

Would you know why my code was causing problems?

Thanks again
Rebecca

[lucas]

I'm guessing its the loop but we'll wait on a true expert to explain it for us. I also re-read your post and your criteria was if cells in col A are blank and this will address that for you.....note it will delete any row with or without data that has a blank in col A

Option Explicit
Sub DeleteBlankColA()
Dim test As Boolean, x As Long, lastrow As Long, col As Long
Range("A1").Select
col = ActiveCell.Column
lastrow = Cells(65536, col).End(xlUp).Row
For x = lastrow To 1 Step -1
test = Cells(x, col).Text Like "[]"
If test = True Then Cells(x, col).EntireRow.Delete
Next
End Sub

[lucas]

Also Rebecca, when you post code be sure to select the code and hit the vba button as I did to your first post in this thread. Makes it easier to read. Also don't forget to mark your thread solved(Use thread tools at the top of the page) if you get your question answered. Good luck and if you still have problems post back in the forum.

[jindon]

Hi
you could change like this, however it is not a good idea to use ActiveCell when you need to delete the row.
It really slows down the process.

Sub DeleteRows()
Range("D1").Select
Do Until ActiveCell.Value = Empty
If ActiveCell.Offset(0, -3) = Empty Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

[MissRibena]

Thanks for the help with this and the info about using the site.

Rebecca

[johnske]

Hi everyone

I think this must be a very basic problem but I just can't figure out why this code won't work. I just want excel to delete any rows where Column A is blank. D is populated to the end of the data.



Sub DeleteRows()
Range("D1").Select
Do Until ActiveCell.Value = Empty
If ActiveCell.Offset(0, -3) = Empty Then
ActiveCell.EntireRow.Delete
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

I tried it this too but Excel still hangs:

Sub DeleteRows()
Range("D1").Select
Do Until ActiveCell.Value = Empty
If ActiveCell.Offset(0, -3) = Empty Then
ActiveCell.EntireRow.Delete xlUp
End If
Loop
End Sub


Has anyone any suggestions as to where I'm going wrong?

Thanks
Rebecca


Hi Rebecca,

The problem can be seen with the help of comments in the code:

Sub DeleteRows()
Range("D1").Select
'
'/activate each cell until an empty one is reached\
Do Until ActiveCell = Empty
'
'/if the cell in column A is empty and the cell in column D is not empty (stated above)\
If ActiveCell.Offset(0, -3) = Empty Then
'
'/then delete this row\
ActiveCell.EntireRow.Delete
'
'the logical error is this: what if neither are empty?
'this leaves visual basic hanging in a tizzy that can be
'resolved with an Else statement (as shown below)
'
Else
'/else if it's not empty and columns A and D both\
'/have an entry the cell below it will be selected\
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub
:)

[lucas]

Thanks John, looks like jindon caught adding the else also

[johnske]

Yep Steve, but I think by her question Rebecca was more likely after an explanation than an actual solution :)

[lucas]

I know, but I couldn't give her the explaination that you have provided so I offered something I knew worked for me...thanks for following up.