Want to delete rows based on a condition

  • Thread starter Thread starter marcia2026
  • Start date Start date
M

marcia2026

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
 
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
 
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
 
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)
 
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"
 
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
 
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.
 
Check your other post.

Steve said:
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.
Click to expand...
 
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
 
Back
Top