Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, _ 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 -- darkwood ------------------------------------------------------------------------ darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948 View this thread: http://www.excelforum.com/showthread...hreadid=531722 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to clear contents of certain cells | Excel Discussion (Misc queries) | |||
Macro to clear contents and put an X | Excel Discussion (Misc queries) | |||
Clear contents macro | Excel Programming | |||
Need macro to clear contents | Excel Programming | |||
Clear Contents Macro | Excel Worksheet Functions |