Want to delete rows based on a condition

Want to delete rows based on a condition

Post by bWFyY2lhMj » Sun, 13 Jul 2008 06:05:01


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

Post by TWlrZSB » Sun, 13 Jul 2008 06:14:00

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

 
 
 

Want to delete rows based on a condition

Post by Gord Dibbe » Sun, 13 Jul 2008 06:18:41

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
 
 
 

Want to delete rows based on a condition

Post by bWFyY2lhMj » Sun, 13 Jul 2008 06:25:01

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)
 
 
 

Want to delete rows based on a condition

Post by bWFyY2lhMj » Sun, 13 Jul 2008 06:27:00

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"
 
 
 

Want to delete rows based on a condition

Post by Gord Dibbe » Sun, 13 Jul 2008 09:00:57

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
 
 
 

Want to delete rows based on a condition

Post by U3RldmUgS2 » Wed, 13 Aug 2008 13:26:04

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
 
 
 

Want to delete rows based on a condition

Post by Dave Peter » Wed, 13 Aug 2008 20:39:32

Check your other post.







--

Dave Peterson
 
 
 

Want to delete rows based on a condition

Post by U2Vla2V » Sun, 01 Nov 2009 19:51:01

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
 
 
 

Want to delete rows based on a condition

Post by U2Vla2V » Sun, 01 Nov 2009 19:54:01

Typo "and = Date - 1", sori as keyboard not functioning very good.