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 ? |
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 ? |
All times are GMT +1. The time now is 11:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com