Excel macro that deletes all rows which doesn't contain the character "|"
 

How can I delete all the rows which column "E" doesn't contain the
character "|" with an Excel macro?

Excel macro that deletes all rows which doesn't contain the character "|"
 

Hi Luca,

Try:

'============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim delRng As Range
Dim rCell As Range
Dim LRow As Long
Dim CalcMode As Long
Const sStr = "|"

Set WB = Workbooks("MyBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1")
LRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = SH.Range("E1:E" & LRow)

On Error GoTo XIT
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rCell In rng.Cells
rCell.Select
If InStr(1, rCell, sStr, vbTextCompare) 0 Then
If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
End If
Next rCell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With
End Sub
'<<============


---
Regards,
Norman


"Luca Villa" wrote in message
oups.com...
How can I delete all the rows which column "E" doesn't contain the
character "|" with an Excel macro?

Excel macro that deletes all rows which doesn't contain the charac
 

Luca,

one way:

Sub test()
Dim rng As Range
Dim c As Range
For Each c In Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("E:E"))
If InStr(1, c.Text, "|") 0 Then
If rng Is Nothing Then
Set rng = c
Else
Set rng = Union(rng, c)
End If
End If
Next c
rng.Rows.Delete
End Sub


--
Hope that helps.

Vergel Adriano


"Luca Villa" wrote:

How can I delete all the rows which column "E" doesn't contain the
character "|" with an Excel macro?

Excel macro that deletes all rows which doesn't contain the charac
 

Adriano, it seems that you're solution only deletes the column "E".
How can I make it delete the entire row in these cases?

Norman, thanks! I'm trying it now.

Excel macro that deletes all rows which doesn't contain the charac
 

Hi Luca,

Adriano, it seems that you're solution only deletes the column "E".
How can I make it delete the entire row in these cases?

Try changing:

rng.Rows.Delete

to:

rng.EntireRow.Delete

---
Regards,
Norman

Excel macro that deletes all rows which doesn't contain the ch
 

Hi Luca,

The code also deleted the ones with "|" in column E but you wanted to do the
opposite <g. Try this :

Sub test()
Dim rng As Range
Dim c As Range
For Each c In Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("E:E"))
If InStr(1, c.Text, "|") = 0 Then
If rng Is Nothing Then
Set rng = c
Else
Set rng = Union(rng, c)
End If
End If
Next c
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If
End Sub


--
Hope that helps.

Vergel Adriano


"Luca Villa" wrote:

Adriano, it seems that you're solution only deletes the column "E".
How can I make it delete the entire row in these cases?

Norman, thanks! I'm trying it now.

Excel macro that deletes all rows which doesn't contain the ch
 

I used this short solution suggested to me in another forum:

Dim r As Range
Dim DelRs As Range

Set r = Range("E1")
Do Until r.Value = ""
If InStr(r.Value, "|") = 0 Then
If Not DelRs Is Nothing Then
Set DelRs = Union(DelRs, r)
Else
Set DelRs = r
End If
End If
Set r = r.Offset(1, 0)
Loop
DelRs.EntireRow.Delete



Thanks to all (Adriano and Norman) anyway for your contribution!