Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign button to clear all, "except"
I want to create a button that, when pressed will clear all rows that have "Y" in column E. How do I accomplish this? I know nothing about macros, so please keep that in mind when explaining. I really appreciate the help! -darkwood -- darkwood ------------------------------------------------------------------------ darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948 View this thread: http://www.excelforum.com/showthread...hreadid=512410 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign button to clear all, "except"
Here is some code that should do what you want. You will need to change the
line that says Sheet2 to the sheet you want to clean. The code assumes that your Y's and N's are in Cells E2 and below... You can change that if you need... 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("Sheet1") 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 You did not mention how you wanted to call the procedure so I can not tell you the best place to put it... -- HTH... Jim Thomlinson "darkwood" wrote: I want to create a button that, when pressed will clear all rows that have "Y" in column E. How do I accomplish this? I know nothing about macros, so please keep that in mind when explaining. I really appreciate the help! -darkwood -- darkwood ------------------------------------------------------------------------ darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948 View this thread: http://www.excelforum.com/showthread...hreadid=512410 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
HELP! Clicking "$" button changes format to ";;" not what it shoul | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Adding a "CLEAR" button to the worksheet | Excel Discussion (Misc queries) | |||
commnd button - when i rt click, "assign macro" isnt an option... | Excel Programming |