Reply to thread

Message: [QUOTE="darkwood, post: 8086446"] I have the following macro that I need to modify so that instead of deleting the whole row, it only clears the data, but still moves those rows containing "N" in column E remain, and are moved up to the top lines (row 3 for me), without deleting the format that I have for cells A1:E50, and clearing all with a "Y" in column E. Does that make sense? I'd also like to add an "are you sure you want to delete anything that has yet to clear?" prompt prior to execution. See below and tia..... Sub ClearData() Dim rngToDelete As Range Dim rngToSearch As Range Dim strFirstAddress As String Dim rngFound As Range Dim wks As Worksheet Set wks = Worksheets("Spending") With wks Set rngToSearch = .Range(.Range("E2"), _ ..Cells(Rows.Count, "E").End(xlUp)) End With Set rngFound = rngToSearch.Find(What:="Y", _ LookAt:=xlWhole, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Nothing to Delete" Else Set rngToDelete = rngFound strFirstAddress = rngFound.Address Do Set rngToDelete = Union(rngFound, rngToDelete) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress rngToDelete.EntireRow.Delete End If End Sub [/QUOTE]