PDA

View Full Version : [SOLVED] Find all cells with certain value and delete the whole row



GP2006
04-05-2018, 02:42 AM
Hi,

I have a messy crystal reports dump in Excell and I want to delete the entire row of every cell which has the word "date" in it. I have this so far:


Sub Macro1()
Range("D1").Select
Selection.Copy
Selection.SpecialCells(xlCellTypeLastCell).Select
Range(Selection, Cells(1)).Select
If Not (Selection.Find(What:="date", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)) Is Nothing Then
Cells.Find(What:="date", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Select
Selection.EntireRow.Delete
End If
End Sub

But this only does it for the first cell it finds with the word "date" in and then stops. I either need it to continu to the next cell until it has done it for all cells in the selection or if there's an easier way to select all cells at once and delete their rows that would be fine too. The word "date" can be in multiple cells in a row.

Thanks.

mana
04-05-2018, 04:09 AM
Option Explicit


Sub test()
Dim r As Range, u As Range

For Each r In ActiveSheet.UsedRange.Rows
If WorksheetFunction.CountIf(r, "date") > 0 Then
If u Is Nothing Then
Set u = r
Else
Set u = Union(u, r)
End If
End If
Next
If Not u Is Nothing Then
u.Delete xlShiftUp
End If

End Sub



マナ

GP2006
04-05-2018, 05:23 AM
Thank you for replying. This works very well, however only when the exact value of the cell is "date", but in my case the values are the word "date" with an actual date behind it, which is different every time.
In the piece of code I posted in the opening post it says LookAt:=xlPart to find cells which have date in part of the value. Maybe I should have made that more clear from the start, sorry about that.

mana
04-05-2018, 05:30 AM
If WorksheetFunction.CountIf(r, "date*") > 0 Then

GP2006
04-05-2018, 06:20 AM
Thanks, but I guess I've got some kind of special situation here where the cell value displayed is different than the actual value so selecting on the word "date" doesn't work. Your code works perfectly for actual cell values but not in this case.
21977

mana
04-05-2018, 07:15 AM
Option Explicit


Sub test2()
Dim r As Range, f As Range, u As Range

Application.FindFormat.Clear
Application.FindFormat.NumberFormat = """date""mm-dd-yy"

For Each r In ActiveSheet.UsedRange.Rows
Set f = r.Find(What:="*", SearchFormat:=True)
If Not f Is Nothing Then
If u Is Nothing Then
Set u = r
Else
Set u = Union(u, r)
End If
End If
Next
If Not u Is Nothing Then
u.Delete xlShiftUp
End If

Application.FindFormat.Clear

End Sub

GP2006
04-05-2018, 07:51 AM
I get error 1004 in Application.FindFormat.NumberFormat = """date""mm-dd-yy"

jolivanes
04-05-2018, 09:08 PM
Maybe attach a file for the people to try their code on. Pictures are very difficult to run code on.

GP2006
04-10-2018, 07:53 AM
I figured it out. Would not have been able to without your help mana, thanks.