Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows, multiple column criteria
I've been using this code to delete rows based upon data in one column. How do I modify the code to include a second column? i.e. delete th row if "either" column "M" _or_ "N" is empty. I've tried changing th Range to ("M:M", "N:N") and it doesn't work. On Error Resume Next Intersect(Rows("2:" & Rows.Count) Range("M:M").SpecialCells(xlBlanks, _ xlTextValues)).EntireRow.Delete Intersect(Rows("2:" & Rows.Count) Range("M:M").SpecialCells(xlConstants, _ xlTextValues)).EntireRow.Delete Intersect(Rows("2:" & Rows.Count) Range("M:M").SpecialCells(xlFormulas, _ xlTextValues + xlErrors + xlLogical)).EntireRow.Delete On Error GoTo 0 Thanks for the help -- Lift Of ----------------------------------------------------------------------- Lift Off's Profile: http://www.excelforum.com/member.php...nfo&userid=824 View this thread: http://www.excelforum.com/showthread.php?threadid=50686 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows, multiple column criteria
Why not just repeat for column N, pick up the stragglers.
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Lift Off" wrote in message ... I've been using this code to delete rows based upon data in one column. How do I modify the code to include a second column? i.e. delete the row if "either" column "M" _or_ "N" is empty. I've tried changing the Range to ("M:M", "N:N") and it doesn't work. On Error Resume Next Intersect(Rows("2:" & Rows.Count), Range("M:M").SpecialCells(xlBlanks, _ xlTextValues)).EntireRow.Delete Intersect(Rows("2:" & Rows.Count), Range("M:M").SpecialCells(xlConstants, _ xlTextValues)).EntireRow.Delete Intersect(Rows("2:" & Rows.Count), Range("M:M").SpecialCells(xlFormulas, _ xlTextValues + xlErrors + xlLogical)).EntireRow.Delete On Error GoTo 0 Thanks for the help. -- Lift Off ------------------------------------------------------------------------ Lift Off's Profile: http://www.excelforum.com/member.php...fo&userid=8249 View this thread: http://www.excelforum.com/showthread...hreadid=506868 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows, multiple column criteria
hi Liff Off
Change Range("M:M") to Range("M:N") regards Yngve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows, multiple column criteria
Yngve: I just tried that, thought it should work but it leaves row only if BOTH column M and N have data. I want to leave the row i EITHER column has data. Bob: Not sure I understand. After the first pass only rows with dat in column M are left. It clears rows with data in N. Any other ideas? Clif -- Lift Of ----------------------------------------------------------------------- Lift Off's Profile: http://www.excelforum.com/member.php...nfo&userid=824 View this thread: http://www.excelforum.com/showthread.php?threadid=50686 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows, multiple column criteria
hi Lift Off
this sub keeps the rows with non values in M/N and values in M/N if not this help, twist it around. Sub ss() Sheets("ppp").Select Dim i As Double i = Cells(Rows.Count, 1).End(xlUp).Row For i = i To 6 Step -1 If Range("M" & i) "" And Range("N" & i) <= "" _ Or Range("N" & i) "" And Range("M" & i) <= "" Then ' do nothing Else Range("M" & i).EntireRow.Delete End If Next i End Sub regards yngve |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows, multiple column criteria
This is what I mean
On Error Resume Next Intersect(Rows("2:" & Rows.Count), Range("M:M").SpecialCells(xlBlanks, _ xlTextValues)).EntireRow.Delete Intersect(Rows("2:" & Rows.Count), Range("M:M").SpecialCells(xlConstants, _ xlTextValues)).EntireRow.Delete Intersect(Rows("2:" & Rows.Count), Range("M:M").SpecialCells(xlFormulas, _ xlTextValues + xlErrors + xlLogical)).EntireRow.Delete On Error GoTo 0 On Error Resume Next Intersect(Rows("2:" & Rows.Count), Range("N:N").SpecialCells(xlBlanks, _ xlTextValues)).EntireRow.Delete Intersect(Rows("2:" & Rows.Count), Range("N:N").SpecialCells(xlConstants, _ xlTextValues)).EntireRow.Delete Intersect(Rows("2:" & Rows.Count), Range("N:N").SpecialCells(xlFormulas, _ xlTextValues + xlErrors + xlLogical)).EntireRow.Delete On Error GoTo 0 -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Lift Off" wrote in message ... Yngve: I just tried that, thought it should work but it leaves rows only if BOTH column M and N have data. I want to leave the row if EITHER column has data. Bob: Not sure I understand. After the first pass only rows with data in column M are left. It clears rows with data in N. Any other ideas? Cliff -- Lift Off ------------------------------------------------------------------------ Lift Off's Profile: http://www.excelforum.com/member.php...fo&userid=8249 View this thread: http://www.excelforum.com/showthread...hreadid=506868 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows, multiple column criteria
Bob: Tried duplicating. Sheet has 35K rows. After running the firs line of code below, using just column M, it deletes all rows wit blanks in column M. Deletion includes rows with data in N if they wer blank in M. 'Intersect(Rows("2:" & Rows.Count) Range("M:M").SpecialCells(xlBlanks, _ xlTextValues)).EntireRow.Delete The only rows that are left after running the total code are rows wit data in column M _and_ N. Looking for code that'll leave rows wit data in column M _or_ N. yngve: Couldn't get the code to run. It locked up "as is". Change the 'step' from -1 to 1 and it doesn't lock up, but runs throug without deleting any rows. Thanks, Clif -- Lift Of ----------------------------------------------------------------------- Lift Off's Profile: http://www.excelforum.com/member.php...nfo&userid=824 View this thread: http://www.excelforum.com/showthread.php?threadid=50686 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows meeting certain criteria in a particular column | Excel Discussion (Misc queries) | |||
Deleting several rows with given criteria | Excel Discussion (Misc queries) | |||
deleting multiple rows with multiple criteria | Excel Programming | |||
Deleting rows based on multiple criteria | Excel Programming | |||
Cut rows from one sheet into multiple sheets based on a criteria in first column | Excel Programming |