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

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

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

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

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

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

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

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

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

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