PDA

View Full Version : Sleeper: Delete row when condition is true



outrider
03-04-2005, 02:11 PM
I am having problems with a project where the worksheet has 400 rows, if any cells in column "G" contain a specific word, then the whole row should be deleted.
The rest of the macro works fine, I just can't seem to get this part right.

Thanks for the help.

Zack Barresse
03-04-2005, 02:31 PM
The rest of the macro works fine ..
I'm worried by this line (before giving any advice). What do you mean exactly? Do you already have some worksheet event code? Is this part of a larger problem/solution?

Paleo
03-04-2005, 04:29 PM
When you are deleting rows you MUST care about the direction your deleting loop goes. If it goes from the smaller row to the bigger it WILL incur in error loosing some rows because when you delete a row, the next row goes up and then the loop advances, this makes it to not check a row. The right way is to go from the bigger to the smaller, something like:



For i = Range("A65536").End(xlUp).Row To 1 Step - 1
... code ...
Next


Sure, as Zack said its hard to help with so little information but I would guess this might solve it.

mdmackillop
03-04-2005, 05:20 PM
Try the following


Option Compare Text

Sub TestIt()
Dim ToFind As String, c As Range, MyRange As Range
ToFind = "Test"
With Intersect(Range("G:G"), ActiveSheet.UsedRange)
Set c = .Find(ToFind, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Set MyRange = c.EntireRow
Do
Set c = .FindNext(c)
Set MyRange = Union(MyRange, c.EntireRow)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
MyRange.Delete
End With
End Sub

Anne Troy
03-04-2005, 06:01 PM
And here's the whole KB article, which may or may not use the same method that MD did. I'm no coder, so I didn't look. :)

http://www.vbaexpress.com/kb/getarticle.php?kb_id=260

mdmackillop
03-04-2005, 06:15 PM
Hi DB
Very similar. I guess I'm needing to familiarise myself with these KB entries a bit better!:whistle:

Anne Troy
03-04-2005, 06:16 PM
Might save you a wee bit of time. :D

Paleo
03-05-2005, 08:39 AM
So outrider,

is it solved?

outrider
03-05-2005, 10:26 AM
I'm worried by this line (before giving any advice). What do you mean exactly? Do you already have some worksheet event code? Is this part of a larger problem/solution?


My system takes a large batch of customer orders, sorts them, adds data for parts, production times, materials etc using lookups from other worksheets, then separates the final data into two groups (Assemblies & single items) in a new workbook, this is where the problem starts.
I knew that the deleting process would need to run backwards to ensure no rows would be missed, it just doesn't work properly yet.

Paleo
03-05-2005, 10:46 AM
Can you post your macro, at least the part of it thats not working properly?

mdmackillop
03-05-2005, 11:43 AM
Hi Outrider,
My suggested code does all the finds before deleting anything, and so does not run backwards. Is there any posibility of hidden characters which would prevent the Find from working?
MD

outrider
03-06-2005, 07:13 AM
Adding code supplied by mdmackillop, I have finally got the thing to work.

Thanks for all the help on this problem.