Removing rows of data based on two collumn match

[JCO]

I have a report that contains anywhere from 2000 to 8000 lines of data. I
need to insert 1-4 as shown as a Sub Routine:

Current code that is done

 

[Claus Busch]

Hi,

Am Wed, 13 Nov 2013 13:28:25 -0600 schrieb JCO:

1 - Cycle from row 2 to end of file
2 - Compare the content of Column F and H (with a complex if...then or
case statement
3 - on false delete the row
4 - on true - go to next row

if you want to delete rows you have to go from last row to row 2
e.g.:
LRow = Cells(Rows.Count, "F").End(xlUp).Row
For i = LRow To 2 Step -1
If 'Here your condition for deleting the row' Then
Rows(i).Delete
End If
Next


Regards
Claus B.

 

[JCO]

I have approx 31 comparrisons to make in the If Statement, however it
appears you can't do that many. I'm getting a compiler error that says to
many line continuations.

 

[JCO]

Disregard. I will add to the line so that I don't use as many continuations

 

[tskogstrom]

Unsure if you solved the case with your last remark, but an advise is, to let the user GUI and worksheet formulas do the comparing and heavy work, and just use VBa to do the check and delete. Excel user GUI is much faster than VBA.

Second, instead of looping, if you can use a "help column" that tells e.g. FALSE if false, and 1 if true. Then you can select all rows to delete in one single line, using e.g.:

ActiveSheet.Range("A1:A1000").SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow.Delete


And yes, IF as formula just allow seven nested, then you had to break it up into several columns instead.

/regards

 

[Claus Busch]

Hi,

Am Tue, 19 Nov 2013 12:46:29 -0800 (PST) schrieb tskogstrom:

And yes, IF as formula just allow seven nested, then you had to break it up into several columns instead.

up to xl2003 that is correct
xl2007 or later the limit for nested functions is 64


Regards
Claus B.

 

[tskogstrom]

Great Claus, love the update, a bit rusty

 

[JCO]

Thanks both of you.
Yes I have this routine finished, thanks for following up. I'm using 2007
and 2010 (on different computers). '
Thanks again


"tskogstrom" wrote in message

Great Claus, love the update, a bit rusty