Large Clear Macro-With A Twist :-)

[John Caines]

Hello All.
I have a spreadsheet that I'm creating as a "To Do" spreadsheet.
What I want to be able to do is have macro buttons to clear individual Rows of data.

The rows for data to be cleared will be,,,
E4:P4
E5:P5
E6:P6 (carried on down until),,
E18:P18
(Miss 2 Rows,,)
Then,, E21:P21
going down continuously to
E35:P35
(Miss 2 Rows again)
Then finally E38:P38
going down continuously to
E52:P52

So,, all in all 43 macro clear buttons.
I have a formula that I can assign to 1 which is this;

Sub RowClear()
    If WorksheetFunction.CountA(Range("E4:P4")) = 0 Then
        MsgBox "No Data in ROW 4 to delete..."
            Else
                If MsgBox("Are you sure you want to delete ROW 4?", vbYesNo + vbCritical) = vbYes Then
            Range("E4:P4").ClearContents
        End If
    End If
End Sub

He's my idea
I have to insert this 43 times into VBA (With amended Range data,, & amended "You want to delete ROW XYZ?,,, statement)........
My idea is,, is there a way to write 1 macro,, that can know if a button is on say ROW 30 it would know to clear just the ROW 30 range (IE E30:P30,,, and ask,,"Are you sure you want to delete ROW 30?"

I think maybe you get the jiest of what I'm saying,, rather than have 43 individual clear macros,, if it could be written into 1?

Thing is,, if it can, I' don't know how

Just an idea anyway.
(Actually,,, I'm just thinking now,, maybe you could have just 1 macro that you could assign just 1 clear macro button to,,, and have a pop up box that said something like,,,
"Insert the row numbers you want to clear"
and then I could just type for example 1,7,9,27,40
Click an OK button,, and it would say,,, "You sure you want to delet these ROWs 1,7,9,27,40?

I click ok,, and it deletes them....
(Just an Idea,,, would be cleaner,, as I wouldn't then have 43 macro clear buttons on my sheet)

I hope the above makes sense.
Many thanks
John Caines

 

[John Caines]

Dryver14,,,
You're no trouble,,, it's me!

i didn't even know about this right click on a tab to insert code,, cool....

Still get the error though,,,, not sure why it isn't deleting data,,,

Maybe I can post a spreadsheet on this thread in a minute

&&&&&&&,,, I like your idea,,,,,,
Also,, 9Let me course more trouble
How about a "Failsafe" build in,,,,
I NEVER want to delete any data in rows,,2,3,19,20,36,37 & 54(AND all after 54),, as these are headings,,,
Just an idea also,,,,,
Over to you Krishnakumar,,, all above me,,,VBA,,,
Looks like something from another world
Many thanks
John Caines

 

[John Caines]

Here's a link to my spreadsheet,, ignore stuff in it,,, it's a work in progress,,, as it says,,
a "To Do" sheet
http://dl.dropbox.com/u/16052166/To Do-v4.zip

Download link above,,,
but like I said,,
I can't seem to get the clear function working
Help!!!!
many Thanks
John Caines

 

[John Caines]

I take it back,,,
It's working!!!!!!!!!!!!!!!!!

 

[John Caines]

Many Thanks Krishnakumar,,
It's working now,,, great stuff...
a really really cool feature.
1 thing though Krishnakumar ,, an amendment,, if possible,,,,

Any chance to insert a fail safe so I don't delete rows I NEVER want to delete?
As a note (I deleted ROW 1 by mistake! My headings)
These are the ROWS I never want deleted,,,
1,2,3,19,20,36,37 & 54(AND all after 54),, as these are headings,,,

If any of these were selected by mistake,,, maybe a pop up could say,,, IE
"You've selected Row "3,19" these are Headings! Cannot delete, please retry

I think this would be a cool feature.
Can be done?
Just an idea.
Many thanks again Krishnakumar,,
AQll the best
John Caines

 

[Krishnakumar]

Many Thanks Krishnakumar,,
It's working now,,, great stuff...
a really really cool feature.
1 thing though Krishnakumar ,, an amendment,, if possible,,,,

Any chance to insert a fail safe so I don't delete rows I NEVER want to delete?
As a note (I deleted ROW 1 by mistake! My headings)
These are the ROWS I never want deleted,,,
1,2,3,19,20,36,37 & 54(AND all after 54),, as these are headings,,,

If any of these were selected by mistake,,, maybe a pop up could say,,, IE
"You've selected Row "3,19" these are Headings! Cannot delete, please retry

I think this would be a cool feature.
Can be done?
Just an idea.
Many thanks again Krishnakumar,,
AQll the best
John Caines

John,

Try this one.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim RowsToClear    As String
    Dim x, i As Long, y
    
    Const RowsToExclude As String = "{1,2,3,19,20,36,37,54}"
    
    RowsToClear = Application.InputBox("Enter the rows separated by comma", "Clear Rows", "1,7,9", Type:=1 + 2)
    
    If RowsToClear = "False" Then Exit Sub
    
    If MsgBox("You sure you want to delet these ROWs " & vbLf & RowsToClear & " ?", vbYesNo + vbInformation) = vbYes Then
        x = Split(RowsToClear, ",")
        On Error Resume Next
        For i = 0 To UBound(x)
            y = Evaluate("=match(" & CLng(x(i)) & "," & RowsToExclude & ",0)")
            If IsError(y) Then
                If CLng(x(i)) < 55 Then
                    Application.Intersect(Range("e:p"), Rows(x(i))).ClearContents
                End If
            End If
        Next
        On Error GoTo 0
    End If
    
End Sub

Replace the previous one with this.

Kris

 

[Krishnakumar]

John,

Let me firstly appologise for jumping on your thread and causing trouble,

Kris,

let me cause some more,

What I mean is, is there a way to make this selection for instance

1:9, 11,13,15:20

If you get my meaning

Try,

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim RowsToClear    As String
    Dim x, i As Long
    
    RowsToClear = Application.InputBox("Enter the rows separated by comma", "Clear Rows", "1,7,9", Type:=1 + 2)
    
    If RowsToClear = "False" Then Exit Sub
    
    If MsgBox("You sure you want to delet these ROWs " & vbLf & RowsToClear & " ?", vbYesNo + vbInformation) = vbYes Then
        x = Split(RowsToClear, ",")
        On Error Resume Next
        For i = 0 To UBound(x)
            Application.Intersect(Range("e:p"), Range(x(i))).ClearContents
        Next
        On Error GoTo 0
    End If
    
End Sub

HTH

 

[John Caines]

Hi Krishnakumar,,
Many thanks again for your reply,
Yes,, I've just come back on the board and seen your reply...
Great stuff!
I've just tried your new code,, it's great,, it now means that Headings will not accidentally be deleted,, a great fail safe feature.
many thanks for this,,
It really makes the spreadsheet seem a lot more professional and a cleaner look than having 40+ clear macro buttons!!
Many thanks again Krishnakumar,,
Brilliant
All the best,
A very grateful
John Caines