rewriting macro to only clear contents

[darkwood]

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

 

[Carim]

Hi,

Replace :
rngToDelete.EntireRow.Delete
By
rngToDelete.ClearContents

HTH
Carim