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.
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.