Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows based on cell value
Hi. I'm trying to create a macro that will delete rows where the
value in column E does not equal "ABC". I always have a different number of rows of data so I can't use a fixed range. Also, rows 1-5 are my header rows so I don't want to touch them--so the macro should start looking at column E in row 6 and continue until it hits a row where column E is blank. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows based on cell value
You could do following:
Sub DelRows() Dim SH As Worksheet Set SH = Worksheets("sheet1") For i = SH.Cells(65536, 5).End(xlUp).Row To 6 Step -1 If LCase(SH.Cells(i, 5).Value) < "abc" Then SH.Rows(i).Delete End If Next i End Sub hth Carlo On Jan 17, 10:19*am, wrote: Hi. *I'm trying to create a macro that will delete rows where the value in column E does not equal "ABC". *I always have a different number of rows of data so I can't use a fixed range. *Also, rows 1-5 are my header rows so I don't want to touch them--so the macro should start looking at column E in row 6 and continue until it hits a row where column E is blank. *Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows based on cell value
On Jan 16, 5:26 pm, carlo wrote:
You could do following: Sub DelRows() Dim SH As Worksheet Set SH = Worksheets("sheet1") For i = SH.Cells(65536, 5).End(xlUp).Row To 6 Step -1 If LCase(SH.Cells(i, 5).Value) < "abc" Then SH.Rows(i).Delete End If Next i End Sub hth Carlo On Jan 17, 10:19 am, wrote: Hi. I'm trying to create a macro that will delete rows where the value in column E does not equal "ABC". I always have a different number of rows of data so I can't use a fixed range. Also, rows 1-5 are my header rows so I don't want to touch them--so the macro should start looking at column E in row 6 and continue until it hits a row where column E is blank. Thanks in advance. For some reason that deleted ALL the rows including those with "ABC" in column E. (It didn't delete the header rows though, which is good.) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows based on cell value
that's weird.
what exactly do you look for? is it really "ABC" or something else? Right now i check if the lowercase of this cell is not equal "abc" so if you have a space or something in it, it would delete it as well. Carlo On Jan 17, 10:43*am, wrote: On Jan 16, 5:26 pm, carlo wrote: You could do following: Sub DelRows() Dim SH As Worksheet Set SH = Worksheets("sheet1") For i = SH.Cells(65536, 5).End(xlUp).Row To 6 Step -1 * * If LCase(SH.Cells(i, 5).Value) < "abc" Then * * * * SH.Rows(i).Delete * * End If Next i End Sub hth Carlo On Jan 17, 10:19 am, wrote: Hi. *I'm trying to create a macro that will delete rows where the value in column E does not equal "ABC". *I always have a different number of rows of data so I can't use a fixed range. *Also, rows 1-5 are my header rows so I don't want to touch them--so the macro should start looking at column E in row 6 and continue until it hits a row where column E is blank. *Thanks in advance. For some reason that deleted ALL the rows including those with "ABC" in column E. *(It didn't delete the header rows though, which is good.)- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows based on cell value
On Jan 16, 6:15 pm, carlo wrote:
that's weird. what exactly do you look for? is it really "ABC" or something else? Right now i check if the lowercase of this cell is not equal "abc" so if you have a space or something in it, it would delete it as well. Carlo On Jan 17, 10:43 am, wrote: On Jan 16, 5:26 pm, carlo wrote: You could do following: Sub DelRows() Dim SH As Worksheet Set SH = Worksheets("sheet1") For i = SH.Cells(65536, 5).End(xlUp).Row To 6 Step -1 If LCase(SH.Cells(i, 5).Value) < "abc" Then SH.Rows(i).Delete End If Next i End Sub hth Carlo On Jan 17, 10:19 am, wrote: Hi. I'm trying to create a macro that will delete rows where the value in column E does not equal "ABC". I always have a different number of rows of data so I can't use a fixed range. Also, rows 1-5 are my header rows so I don't want to touch them--so the macro should start looking at column E in row 6 and continue until it hits a row where column E is blank. Thanks in advance. For some reason that deleted ALL the rows including those with "ABC" in column E. (It didn't delete the header rows though, which is good.)- Hide quoted text - - Show quoted text - Yes, I am actually looking for "ABC" (This is for a list of TV shows with each show getting a row. Column E is the network for each show and I want to eliminate those on all networks except ABC. I'll also use this to eliminate all but those on FOX, etc. but I figured I could just swap out the network name in the macro.) One thing I'm confused about (and I'm relatively inexperienced in dealing with macros) is that the macro you wrote doesn't seem to mention column E anywhere--is that correct? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows based on cell value
Hi Brian
Ok....just wanted to make sure. For your question about column E, you're right it doesn't say E, because i use the columnNumber 5 (in rows 3 and 4) which works the same way. Try this macro and tell me, what output you get in the immediate Window (if you don't see it, press Ctrl + G) Sub DelRows() Dim SH As Worksheet Set SH = Worksheets("sheet1") For i = SH.Cells(65536, 5).End(xlUp).Row To 6 Step -1 Debug.print SH.cells(i,5).value, LCase(SH.Cells(i, 5).Value) < "abc" If LCase(SH.Cells(i, 5).Value) < "abc" Then SH.Rows(i).Delete End If Next i End Sub Is there a possibility of a space in front or after "ABC"? Carlo On Jan 18, 2:38*am, wrote: On Jan 16, 6:15 pm, carlo wrote: that's weird. what exactly do you look for? is it really "ABC" or something else? Right now i check if the lowercase of this cell is not equal "abc" so if you have a space or something in it, it would delete it as well. Carlo On Jan 17, 10:43 am, wrote: On Jan 16, 5:26 pm, carlo wrote: You could do following: Sub DelRows() Dim SH As Worksheet Set SH = Worksheets("sheet1") For i = SH.Cells(65536, 5).End(xlUp).Row To 6 Step -1 * * If LCase(SH.Cells(i, 5).Value) < "abc" Then * * * * SH.Rows(i).Delete * * End If Next i End Sub hth Carlo On Jan 17, 10:19 am, wrote: Hi. *I'm trying to create a macro that will delete rows where the value in column E does not equal "ABC". *I always have a different number of rows of data so I can't use a fixed range. *Also, rows 1-5 are my header rows so I don't want to touch them--so the macro should start looking at column E in row 6 and continue until it hits a row where column E is blank. *Thanks in advance. For some reason that deleted ALL the rows including those with "ABC" in column E. *(It didn't delete the header rows though, which is good.)- Hide quoted text - - Show quoted text - Yes, I am actually looking for "ABC" (This is for a list of TV shows with each show getting a row. *Column E is the network for each show and I want to eliminate those on all networks except ABC. *I'll also use this to eliminate all but those on FOX, etc. but I figured I could just swap out the network name in the macro.) One thing I'm confused about (and I'm relatively inexperienced in dealing with macros) is that the macro you wrote doesn't seem to mention column E anywhere--is that correct?- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows based on cell value
On Jan 17, 4:13 pm, carlo wrote:
Hi Brian Ok....just wanted to make sure. For your question about column E, you're right it doesn't say E, because i use the columnNumber 5 (in rows 3 and 4) which works the same way. Try this macro and tell me, what output you get in the immediate Window (if you don't see it, press Ctrl + G) Sub DelRows() Dim SH As Worksheet Set SH = Worksheets("sheet1") For i = SH.Cells(65536, 5).End(xlUp).Row To 6 Step -1 Debug.print SH.cells(i,5).value, LCase(SH.Cells(i, 5).Value) < "abc" If LCase(SH.Cells(i, 5).Value) < "abc" Then SH.Rows(i).Delete End If Next i End Sub Is there a possibility of a space in front or after "ABC"? Carlo On Jan 18, 2:38 am, wrote: On Jan 16, 6:15 pm, carlo wrote: that's weird. what exactly do you look for? is it really "ABC" or something else? Right now i check if the lowercase of this cell is not equal "abc" so if you have a space or something in it, it would delete it as well. Carlo On Jan 17, 10:43 am, wrote: On Jan 16, 5:26 pm, carlo wrote: You could do following: Sub DelRows() Dim SH As Worksheet Set SH = Worksheets("sheet1") For i = SH.Cells(65536, 5).End(xlUp).Row To 6 Step -1 If LCase(SH.Cells(i, 5).Value) < "abc" Then SH.Rows(i).Delete End If Next i End Sub hth Carlo On Jan 17, 10:19 am, wrote: Hi. I'm trying to create a macro that will delete rows where the value in column E does not equal "ABC". I always have a different number of rows of data so I can't use a fixed range. Also, rows 1-5 are my header rows so I don't want to touch them--so the macro should start looking at column E in row 6 and continue until it hits a row where column E is blank. Thanks in advance. For some reason that deleted ALL the rows including those with "ABC" in column E. (It didn't delete the header rows though, which is good.)- Hide quoted text - - Show quoted text - Yes, I am actually looking for "ABC" (This is for a list of TV shows with each show getting a row. Column E is the network for each show and I want to eliminate those on all networks except ABC. I'll also use this to eliminate all but those on FOX, etc. but I figured I could just swap out the network name in the macro.) One thing I'm confused about (and I'm relatively inexperienced in dealing with macros) is that the macro you wrote doesn't seem to mention column E anywhere--is that correct?- Hide quoted text - - Show quoted text - Thanks Carlo. That actually didn't work for me either. I can't figure out exactly what's going wrong. I think I've found a way to handle this using autofilter though. Thanks for all your help. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows based on cell value
On Jan 18, 10:51*am, wrote:
On Jan 17, 4:13 pm, carlo wrote: Hi Brian Ok....just wanted to make sure. For your question about column E, you're right it doesn't say E, because i use the columnNumber 5 (in rows 3 and 4) which works the same way. Try this macro and tell me, what output you get in the immediate Window (if you don't see it, press Ctrl + G) Sub DelRows() Dim SH As Worksheet Set SH = Worksheets("sheet1") For i = SH.Cells(65536, 5).End(xlUp).Row To 6 Step -1 * * Debug.print SH.cells(i,5).value, LCase(SH.Cells(i, 5).Value) < "abc" * * If LCase(SH.Cells(i, 5).Value) < "abc" Then * * * * SH.Rows(i).Delete * * End If Next i End Sub Is there a possibility of a space in front or after "ABC"? Carlo On Jan 18, 2:38 am, wrote: On Jan 16, 6:15 pm, carlo wrote: that's weird. what exactly do you look for? is it really "ABC" or something else? Right now i check if the lowercase of this cell is not equal "abc" so if you have a space or something in it, it would delete it as well. Carlo On Jan 17, 10:43 am, wrote: On Jan 16, 5:26 pm, carlo wrote: You could do following: Sub DelRows() Dim SH As Worksheet Set SH = Worksheets("sheet1") For i = SH.Cells(65536, 5).End(xlUp).Row To 6 Step -1 * * If LCase(SH.Cells(i, 5).Value) < "abc" Then * * * * SH.Rows(i).Delete * * End If Next i End Sub hth Carlo On Jan 17, 10:19 am, wrote: Hi. *I'm trying to create a macro that will delete rows where the value in column E does not equal "ABC". *I always have a different number of rows of data so I can't use a fixed range. *Also, rows 1-5 are my header rows so I don't want to touch them--so the macro should start looking at column E in row 6 and continue until it hits a row where column E is blank. *Thanks in advance. For some reason that deleted ALL the rows including those with "ABC" in column E. *(It didn't delete the header rows though, which is good.)- Hide quoted text - - Show quoted text - Yes, I am actually looking for "ABC" (This is for a list of TV shows with each show getting a row. *Column E is the network for each show and I want to eliminate those on all networks except ABC. *I'll also use this to eliminate all but those on FOX, etc. but I figured I could just swap out the network name in the macro.) One thing I'm confused about (and I'm relatively inexperienced in dealing with macros) is that the macro you wrote doesn't seem to mention column E anywhere--is that correct?- Hide quoted text - - Show quoted text - Thanks Carlo. *That actually didn't work for me either. *I can't figure out exactly what's going wrong. *I think I've found a way to handle this using autofilter though. *Thanks for all your help.- Hide quoted text - - Show quoted text - Well....i didn't change the code...i just wanted to see what the output would be. But if you can do it with autofilter, no problem. Cheers Carlo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I conditionally delete rows based on cell contents? | Excel Worksheet Functions | |||
Delete Rows based on cell values. | Excel Programming | |||
Delete duplicate rows based on part of cell. | Excel Programming | |||
Delete rows based on Cell name | Excel Programming | |||
Hide/Delete entire rows based in the content of one cell | Excel Discussion (Misc queries) |