Macro deleting rows including some valu/ewpression

  • Thread starter Thread starter Snoopy
  • Start date Start date
S

Snoopy

Hey guys
In some of the cells in column A - Range("A2:A") - there are
expressions including the frace/part: "\old\".
I want to remove all rows including this frace and keep the remaining
ones.

The nearest I can get is something like this, but (of cause) my
attempt was not very successful.
(Yeah - I know I stole/borrowed it from someone else and tryed to
ajust it ...forgive me)

Sub Delete_OLD_rows()
Dim cell As Range
Dim delRange As Range
For Each cell In Range("A2:B" & Range("A" & Rows.Count).End
(xlUp).Row)
If cell.Value = ""*\OLD\* Then
If delRange Is Nothing Then
Set delRange = cell
Else
Set delRange = Union(delRange, cell)
End If
End If
Next cell
If Not delRange Is Nothing Then delRange.EntireRow.Delete
End Sub

Wil you please help me?
Best regards
Snoopy
 
Snoopy,

There is no such thing as "stealing" code, if it has been posted. It is there for you to use and
learn from, so steal away....

Anyway, here is how you would do it within your framework. You could also use InStr, or step up the
rows from bottom to top, deleting as you go, or you could sort first, and do a block deletion,
filter and delete, etc. There are a LOT of different ways.

Sub Delete_OLD_rows()
Dim cell As Range
Dim delRange As Range
For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
If UCase(cell.Value) Like "*\OLD\*" Then
If delRange Is Nothing Then
Set delRange = cell
Else
Set delRange = Union(delRange, cell)
End If
End If
Next cell
If Not delRange Is Nothing Then delRange.EntireRow.Delete
End Sub


HTH,
Bernie
MS Excel MVP
 
Back
Top