rewriting macro to only clear contents



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, _
If rngFound Is Nothing Then
MsgBox "Nothing to Delete"
Set rngToDelete = rngFound
strFirstAddress = rngFound.Address
Set rngToDelete = Union(rngFound, rngToDelete)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
End Sub


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question