Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing rows of data based on two collumn match
Great Claus, love the update, a bit rusty :-)
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing rows but not based on a range | Excel Programming | |||
Filling in cells (or not) based on the entry in a specific collumn | Excel Discussion (Misc queries) | |||
Removing rows based on isDate | Excel Programming | |||
counting if data from one collumn is present in another collumn | Excel Worksheet Functions | |||
How to find out how many rows do my collumn have? | Excel Programming |