Hi,
I'm trying to write a macro that deletes a row if column B does not contain the word "DOGS"
There is a different number of rows every day.
Any help would be greatly appreciated,
Thanks!
Hi,
I'm trying to write a macro that deletes a row if column B does not contain the word "DOGS"
There is a different number of rows every day.
Any help would be greatly appreciated,
Thanks!
Hi Tesseh (and fellow Sydneyite),
Try this (initially on a copy of your data as results cannot be undone if they're not as expected):
Note that the code is looking for the text 'DOGS' in that case. If your not worried about case sensitivity (i.e. 'Dogs', 'dogs', etc...), change this line of code...![]()
Option Explicit Sub Macro2() 'Written by Trebor76 'Visit my website wwww.excelguru.net.au 'Delete all rows where the text 'DOGS' is not in Col. B from the row number passed to the constant variable 'lngStartRow'. Const lngStartRow As Long = 2 'Starting row number for your data. Change to suit. Dim lngMyCol As Long, _ lngMyRow As Long Dim xlnCalcMethod As XlCalculation With Application xlnCalcMethod = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1 lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row With Columns(lngMyCol) With Range(Cells(lngStartRow, lngMyCol), Cells(lngMyRow, lngMyCol)) .Formula = "=IFERROR(FIND(""DOGS"",B" & lngStartRow & "),NA())" ActiveSheet.Calculate .Value = .Value End With On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete On Error GoTo 0 'Turn error reporting back on .Delete End With With Application .Calculation = xlnCalcMethod .ScreenUpdating = True End With MsgBox "All applicable rows have now been deleted.", vbInformation, "Excel Guru" End Sub
...to this:![]()
.Formula = "=IFERROR(FIND(""DOGS"",B" & lngStartRow & "),NA())"
Regards,![]()
.Formula = "=IFERROR(SEARCH(""DOGS"",B" & lngStartRow & "),NA())"
Robert
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
Robert, did I ever thank you for this? I use this macro everyday and it works perfectly. Thanks so much!!!!
No, I didn't think you ever did but that's fine - you have now
If you could mark the thread as closed it would be appreciated.
Thanks,
Robert
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks