Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing lines of data that do not contain required data (macro)
I would like to delete rows of data that do NOT contain
the word "Total" in column A. An earlier response from Rob suggested the following would work: Sub test() Dim rng As Range, i As Long With ActiveSheet For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row If LCase(.Cells(i, 1).Value) Like "*total*" Then If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i)) End If Next If Not rng Is Nothing Then rng.Delete xlShiftUp End With End Sub however, this Deletes the rows that contain the word "Total" and keeps the other non "Total" rows (which is the opposite of what I want. Any ideas ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing lines of data that do not contain required data (macro)
Hi
replace the line If LCase(.Cells(i, 1).Value) Like "*total*" Then with the following statement: If InStr(LCase(.Cells(i, 1).Value),"total")=0 Then -- Regards Frank Kabel Frankfurt, Germany "Sean" schrieb im Newsbeitrag ... I would like to delete rows of data that do NOT contain the word "Total" in column A. An earlier response from Rob suggested the following would work: Sub test() Dim rng As Range, i As Long With ActiveSheet For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row If LCase(.Cells(i, 1).Value) Like "*total*" Then If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i)) End If Next If Not rng Is Nothing Then rng.Delete xlShiftUp End With End Sub however, this Deletes the rows that contain the word "Total" and keeps the other non "Total" rows (which is the opposite of what I want. Any ideas ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Data Tables formed from importing data from Access | Excel Discussion (Misc queries) | |||
Need macro for copying random lines of data | Excel Discussion (Misc queries) | |||
Help Required!!! Macro to load data from version 1 to version 2 | Excel Worksheet Functions | |||
Macro to copy range of formulas to equal data lines | Excel Worksheet Functions | |||
Scanning for Duplicate Data in a Column, Merging Data and Finally Removing All Duplicates...? | Excel Programming |