Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Macro that deletes rows with "0" value cells | Excel Programming | |||
What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell? | Excel Programming | |||
What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell? | Excel Worksheet Functions | |||
Macro to insert "0" in front of 4 character string | Excel Programming |