Removing rows of data based on two collumn match
 

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

Removing rows of data based on two collumn match
 

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Removing rows of data based on two collumn match
 

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.

"Claus Busch" wrote in message
...
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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Removing rows of data based on two collumn match
 

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

"Claus Busch" wrote in message
...
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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Removing rows of data based on two collumn match
 

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(xlCellT ypeFormulas, xlNumbers).EntireRow.Delete


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

/regards

Removing rows of data based on two collumn match
 

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Removing rows of data based on two collumn match
 

Great Claus, love the update, a bit rusty :-)

Removing rows of data based on two column match
 

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 :-)