Macro for fast deletion of rows that conatin certain data.

P

PhilBennett

I need a macro that deletes all rows in a worksheet in which a certain text
string is found in a cell in that row? e.g. If a cell in column "B" contains
a text string "6:00 AM", then delete the entire row.
 
J

Jacob Skaria

Try the below

Sub DeleteRows()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1
If Range("B" & lngRow).Text = "6:00 AM" Then Rows(lngRow).Delete
Next
End Sub

If this post helps click Yes
 
R

Roger Govier

Hi Phil

I would loop through the file starting at the last row and build a
collection of the rows with the text 6:00 AM in them, then delete that
collection at the finish of the routine.

Sub DeleteRows6AM()
Dim lr As Long, i As Long
Dim delrng As Range, ws As Worksheet
Set ws = ActiveSheet
lr = ws.Cells(Rows.Count, 2).End(xlUp).Row
For i = lr To 1 Step -1
If Cells(i, 2).Text = "6:00 AM" Then
If delrng Is Nothing Then
Set delrng = ws.Rows(i)
Else
Set delrng = Application.Union(delrng, ws.Rows(i))
End If
End If
Next
delrng.Delete
End Sub

--
Regards
Roger Govier

PhilBennett said:
I need a macro that deletes all rows in a worksheet in which a certain
text
string is found in a cell in that row? e.g. If a cell in column "B"
contains
a text string "6:00 AM", then delete the entire row.

__________ Information from ESET Smart Security, version of virus
signature database 4527 (20091020) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4527 (20091020) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top