Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to delete rows based on a condition
I am trying to do a Loop/Until macro in order to delete all rows with a
certain value in a certain cell. The only thing I can find on deleting rows requires me to specify the row number. That will be variable |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to delete rows based on a condition
Hi,
Deleting certain rows if cetain cells contain a certain value isn't very helpful to you in finding a solution but maybe you can build on this. It searches the used range of column A for the word Test and deletes every row were it finds it. Right click your sheet tab, view code and paste it in. Sub stantial() Dim myrange, MyRange1 As Range lastrow = Cells(Rows.Count, "A").End(xlUp).Row Set myrange = Sheets("Sheet1").Range("A1:A" & lastrow) For Each c In myrange If UCase(c.Value) = "TEST" Then If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else Set MyRange1 = Union(MyRange1, c.EntireRow) End If End If Next If Not MyRange1 Is Nothing Then MyRange1.Delete End If End Sub "marcia2026" wrote: I am trying to do a Loop/Until macro in order to delete all rows with a certain value in a certain cell. The only thing I can find on deleting rows requires me to specify the row number. That will be variable |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to delete rows based on a condition
I don't understand the "certain cell" bit.
A certain cell should have an address like A1 or B23 Maybe you meant certain column? This will delete all rows with a certain value in any cell in Column A Sub DeleteRows_With_Param() FindString = "qwerty" Set b = Range("A:A").Find(what:=FindString, lookat:=xlWhole) While Not (b Is Nothing) b.entirerow.Delete Set b = Range("A:A").Find(what:=FindString, lookat:=xlWhole) Wend End Sub Gord Dibben MS Excel MVP On Fri, 11 Jul 2008 14:05:01 -0700, marcia2026 wrote: I am trying to do a Loop/Until macro in order to delete all rows with a certain value in a certain cell. The only thing I can find on deleting rows requires me to specify the row number. That will be variable |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to delete rows based on a condition
What I want to do is delete records that have a particular value in Col E.
and delete all of the blank rows in the same table. (that part is new) "Gord Dibben" wrote: I don't understand the "certain cell" bit. A certain cell should have an address like A1 or B23 Maybe you meant certain column? This will delete all rows with a certain value in any cell in Column A Sub DeleteRows_With_Param() FindString = "qwerty" Set b = Range("A:A").Find(what:=FindString, lookat:=xlWhole) While Not (b Is Nothing) b.entirerow.Delete Set b = Range("A:A").Find(what:=FindString, lookat:=xlWhole) Wend End Sub Gord Dibben MS Excel MVP On Fri, 11 Jul 2008 14:05:01 -0700, marcia2026 wrote: I am trying to do a Loop/Until macro in order to delete all rows with a certain value in a certain cell. The only thing I can find on deleting rows requires me to specify the row number. That will be variable |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to delete rows based on a condition
Mike, thanks this worked great. Just what I wanted. Now I need to take it
one step farther and delete all of the totally blank rows in the table. Sorry to be such a pest but I am a "baby newbie" "Mike H" wrote: Hi, Deleting certain rows if cetain cells contain a certain value isn't very helpful to you in finding a solution but maybe you can build on this. It searches the used range of column A for the word Test and deletes every row were it finds it. Right click your sheet tab, view code and paste it in. Sub stantial() Dim myrange, MyRange1 As Range lastrow = Cells(Rows.Count, "A").End(xlUp).Row Set myrange = Sheets("Sheet1").Range("A1:A" & lastrow) For Each c In myrange If UCase(c.Value) = "TEST" Then If MyRange1 Is Nothing Then Set MyRange1 = c.EntireRow Else Set MyRange1 = Union(MyRange1, c.EntireRow) End If End If Next If Not MyRange1 Is Nothing Then MyRange1.Delete End If End Sub "marcia2026" wrote: I am trying to do a Loop/Until macro in order to delete all rows with a certain value in a certain cell. The only thing I can find on deleting rows requires me to specify the row number. That will be variable |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to delete rows based on a condition
Change "A:A" to "E:E"
Change "qwerty" to what you want. To get rid of all entirely blank rows.............. Sub DeleteEmptyRows() ''only if entire row is blank LastRow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For r = LastRow To 1 Step -1 If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete End If Next r End Sub Gord On Fri, 11 Jul 2008 14:25:01 -0700, marcia2026 wrote: What I want to do is delete records that have a particular value in Col E. and delete all of the blank rows in the same table. (that part is new) "Gord Dibben" wrote: I don't understand the "certain cell" bit. A certain cell should have an address like A1 or B23 Maybe you meant certain column? This will delete all rows with a certain value in any cell in Column A Sub DeleteRows_With_Param() FindString = "qwerty" Set b = Range("A:A").Find(what:=FindString, lookat:=xlWhole) While Not (b Is Nothing) b.entirerow.Delete Set b = Range("A:A").Find(what:=FindString, lookat:=xlWhole) Wend End Sub Gord Dibben MS Excel MVP On Fri, 11 Jul 2008 14:05:01 -0700, marcia2026 wrote: I am trying to do a Loop/Until macro in order to delete all rows with a certain value in a certain cell. The only thing I can find on deleting rows requires me to specify the row number. That will be variable |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to delete rows based on a condition
Gord
If I may equire further, I wonder if this script can be added too. I am looking to delete any row where the number in column D is less than 96 AND column E has ANY notes. If this makes it easier, ANY row that has a number above 96 in column D or column E is blank (Looking for notes in that column), I want to keep. Delete all others. I am a super novice on VB so any help would be greatly appreciated. -- Thanks Steve "Gord Dibben" wrote: Change "A:A" to "E:E" Change "qwerty" to what you want. To get rid of all entirely blank rows.............. Sub DeleteEmptyRows() ''only if entire row is blank LastRow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For r = LastRow To 1 Step -1 If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete End If Next r End Sub Gord On Fri, 11 Jul 2008 14:25:01 -0700, marcia2026 wrote: What I want to do is delete records that have a particular value in Col E. and delete all of the blank rows in the same table. (that part is new) "Gord Dibben" wrote: I don't understand the "certain cell" bit. A certain cell should have an address like A1 or B23 Maybe you meant certain column? This will delete all rows with a certain value in any cell in Column A Sub DeleteRows_With_Param() FindString = "qwerty" Set b = Range("A:A").Find(what:=FindString, lookat:=xlWhole) While Not (b Is Nothing) b.entirerow.Delete Set b = Range("A:A").Find(what:=FindString, lookat:=xlWhole) Wend End Sub Gord Dibben MS Excel MVP On Fri, 11 Jul 2008 14:05:01 -0700, marcia2026 wrote: I am trying to do a Loop/Until macro in order to delete all rows with a certain value in a certain cell. The only thing I can find on deleting rows requires me to specify the row number. That will be variable |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to delete rows based on a condition
Check your other post.
Steve Kellogg wrote: Gord If I may equire further, I wonder if this script can be added too. I am looking to delete any row where the number in column D is less than 96 AND column E has ANY notes. If this makes it easier, ANY row that has a number above 96 in column D or column E is blank (Looking for notes in that column), I want to keep. Delete all others. I am a super novice on VB so any help would be greatly appreciated. -- Thanks Steve "Gord Dibben" wrote: Change "A:A" to "E:E" Change "qwerty" to what you want. To get rid of all entirely blank rows.............. Sub DeleteEmptyRows() ''only if entire row is blank LastRow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For r = LastRow To 1 Step -1 If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete End If Next r End Sub Gord On Fri, 11 Jul 2008 14:25:01 -0700, marcia2026 wrote: What I want to do is delete records that have a particular value in Col E. and delete all of the blank rows in the same table. (that part is new) "Gord Dibben" wrote: I don't understand the "certain cell" bit. A certain cell should have an address like A1 or B23 Maybe you meant certain column? This will delete all rows with a certain value in any cell in Column A Sub DeleteRows_With_Param() FindString = "qwerty" Set b = Range("A:A").Find(what:=FindString, lookat:=xlWhole) While Not (b Is Nothing) b.entirerow.Delete Set b = Range("A:A").Find(what:=FindString, lookat:=xlWhole) Wend End Sub Gord Dibben MS Excel MVP On Fri, 11 Jul 2008 14:05:01 -0700, marcia2026 wrote: I am trying to do a Loop/Until macro in order to delete all rows with a certain value in a certain cell. The only thing I can find on deleting rows requires me to specify the row number. That will be variable -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to delete rows based on a condition
Hi Gord,
I would like to adopt your code also and tried in column B with = Date and =Date 1 both work great, however, I would like to delet rows with Date less than today but I was rejected with the "<", how could I accomplish the less than effect please? Rgds "Gord Dibben" wrote: I don't understand the "certain cell" bit. A certain cell should have an address like A1 or B23 Maybe you meant certain column? This will delete all rows with a certain value in any cell in Column A Sub DeleteRows_With_Param() FindString = "qwerty" Set b = Range("A:A").Find(what:=FindString, lookat:=xlWhole) While Not (b Is Nothing) b.entirerow.Delete Set b = Range("A:A").Find(what:=FindString, lookat:=xlWhole) Wend End Sub Gord Dibben MS Excel MVP On Fri, 11 Jul 2008 14:05:01 -0700, marcia2026 wrote: I am trying to do a Loop/Until macro in order to delete all rows with a certain value in a certain cell. The only thing I can find on deleting rows requires me to specify the row number. That will be variable |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Want to delete rows based on a condition
Typo "and = Date - 1", sori as keyboard not functioning very good.
"Seeker" wrote: Hi Gord, I would like to adopt your code also and tried in column B with = Date and =Date 1 both work great, however, I would like to delet rows with Date less than today but I was rejected with the "<", how could I accomplish the less than effect please? Rgds "Gord Dibben" wrote: I don't understand the "certain cell" bit. A certain cell should have an address like A1 or B23 Maybe you meant certain column? This will delete all rows with a certain value in any cell in Column A Sub DeleteRows_With_Param() FindString = "qwerty" Set b = Range("A:A").Find(what:=FindString, lookat:=xlWhole) While Not (b Is Nothing) b.entirerow.Delete Set b = Range("A:A").Find(what:=FindString, lookat:=xlWhole) Wend End Sub Gord Dibben MS Excel MVP On Fri, 11 Jul 2008 14:05:01 -0700, marcia2026 wrote: I am trying to do a Loop/Until macro in order to delete all rows with a certain value in a certain cell. The only thing I can find on deleting rows requires me to specify the row number. That will be variable |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows based on condition | Excel Discussion (Misc queries) | |||
Macro to delete rows based on a condition | Excel Worksheet Functions | |||
Delete worksheet row based on condition | Excel Programming | |||
How to delete a data row based on a condition | New Users to Excel | |||
Delete Columns based on a condition | Excel Programming |