Removing rows of data based on two collumn match

  • Thread starter Thread starter JCO
  • Start date Start date
J

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
 
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
Click to expand...

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.
 
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.
 
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
 
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.
Click to expand...

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


Regards
Claus B.
 
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 :-)
 
Back
Top