deleting rows using excel macro – repost

Topic

New Reply

Hi
I have a set of data, where I want to scroll through the rows, and delete the ones that have the word “BlanK” in column C. I would also like to stop when a cell in column C is empty.The data are imported from a .csv file and are in General format. A newbie, I am still not fully versed in the VBA syntax and I have tried a couple of things but can’t seem to get it to work correctly. It seems to me that this should be rather simple but I have already blown a couple of hours on this. Any suggestions would be greatly appreciated.

I tried this but it doesn’t seem to recognize “Blank” or Blank. There also may be an issue when a row is deleted (the old active cell is now the new active cell without being offset. I am using Excel 2007.

Sub DeleteRows()

Range(“c5”).Activate

Do While ActiveCell.Value Empty
If ActiveCell.Value = “Blank” Then
Rows(ActiveCell.Row).Delete
Else
ActiveCell = ActiveCell.Offset(1, 0).Select
End If

ActiveCell = ActiveCell.Offset(1, 0).Select
Loop

End Sub

Reply | Quote

Replies

When deleting items from a collection, the general rule is to delete from the end back to the beginning. This modified version of your existing code, however does delete without problems…
‘—
Sub DeleteRows()
Range(“C5”).Activate

Do While Not IsEmpty(ActiveCell)
If ActiveCell.Value = “Blank” Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
‘—
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Extras for Excel add-in: convenience built-in)

Reply | Quote

Sub DeleteRows()
Jim
Thanks so much for the help. The code you sent works fine as long as there are not consecutive “Blank” rows. In that case it deletes the first row and skips the second row. I added one line of code to back up the active cell if a row is deleted. That seems to do the trick.
Thanks very much for the help. I can see this will be a long learning curve!
Arjay13

Range(“C5”).Activate

Do While Not IsEmpty(ActiveCell)
If ActiveCell.Value = “Blank” Then
ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
End If

ActiveCell.Offset(1, 0).Select

Loop
End Sub

Reply | Quote

You might to also try this flavor…

Range(“C5”).Activate
Do While Not IsEmpty(ActiveCell)
If ActiveCell.Value = “Blank” Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

Reply | Quote

Ah Yes. In the back of my tiny mind, I was thinking about an Else…nicely done

Reply | Quote