Autodelete whenever 'XYZ' occurs

  • Thread starter Thread starter bLySs
  • Start date Start date
B

bLySs

Hi

I need to find some way to delete certain rows in excel automatically
The data that i have contains some gibberish every now and then whic
doesn't occur in a repeating pattern.
However, it is always marked by" <square> Persh "(when i say square,
mean the symbol, there's a little square there).

I was wondering if there was some way to write a macros so tha
whenever it came across " <square> Persh " in column A it would selec
that row and 11 rows following it, delete it, and shift the remain
cells back up.

Any ideas anyone?
Much appreciated
Blyss

(i was hoping to delete it before I imported the data into excel, bu
it seems a tad complicated
 
I think you'll want to find out what that little square is.

You can find a nice utility from Chip Pearson at:
http://www.cpearson.com/excel/CellView.htm

I guessed that the square was chr(13)--but it may not be!

Option Explicit
Sub testme01()

Dim myCell As Range
Dim delRng As Range
Dim wks As Worksheet
Dim NumberOfRowsToDelete As Long

NumberOfRowsToDelete = 12

Set wks = Worksheets("sheet1")

With wks
For Each myCell In .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
If LCase(myCell.Value) = " " & Chr(13) & " persh " Then
If delRng Is Nothing Then
Set delRng = myCell.Resize(NumberOfRowsToDelete)
Else
Set delRng _
= Union(myCell.Resize(NumberOfRowsToDelete), delRng)
End If
End If
Next myCell
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireRow.Select
'delRng.EntireRow.Delete
End If

End Sub

Comment that .select line and uncomment the .delete line after you've verified
that it works the way you want.
 
Back
Top