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

 

[Krishnakumar]

Hi,

Put this code in Sheet module.

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"), Rows(x(i))).ClearContents
        Next
        On Error GoTo 0
    End If
    
End Sub

To call the macro, double any cell.

HTH

 

[CWatts]

Hey John! The input box may be a good way to go. For example, you can get a string and then write a loop to populate the KillList array, use the MsgBox to output a vbYesNo for confirmation and then another for loop to cycle through and clear the row contents you want.

Sub BoxStuff()
Dim RowList As String
Dim KillList(1 To 100) As Integer

RowList = InputBox("Enter the sheets to delete, separated by a comma:", "Enter...")

End Sub

Edit: Or go with the complete solution that snuck in ahead of me.

 

[Dryver14]

KrishnaKumar,

I really like that bit of coding, what would I need to do to it to select a range eg A1:D4?

 

[Krishnakumar]

Hi,

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

    Dim RowsToClear    As String
    Dim x, i As Long
    
    If Not Application.Intersect(Target.Rows, Range("a1:d4")) Is Nothing Then
    
    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("a1:d4"), Rows(x(i))).ClearContents
        Next
        On Error GoTo 0
    End If
    End If
End Sub

 

[John Caines]

Many many thanks for both your replies...!!! Great stuff,, really

Thing is,,, I can't get it to show up in macros??

(I'm a bit of a novice here,, please bare with me here,,,)
1. I click Developer,, then open Visual Basic.
2. In Visual Basic I right click on Sheet1 (To Do) & select "Insert Module",, then paste in KrishnaKumar's code.
(This has now created a Module 1)
3. I click save,, and close Visual Basic.
4. Now,, I go "Insert",,, select "Form Controls" & select button.
5. I try to assign macro,,, I now right click on the button (Which I've place on my sheet "To Do",,, and select "Assign Macro",,, then I paste in the words "Worksheet_BeforeDoubleClick",,,, so now the full macro name in the assign macro pop up is;
'To Do-v3-Copy-Copy.xlsm'!Worksheet_BeforeDoubleClick

6.I then click on the macro button and I get
"Argument not optional" in a pop up box??????????

Help!!!
Not sure what I've done wrong here,,,
can someone just tell me if I need to change something,, or do something in another order please.

Many thanks again for all your replies,,,,
I hope I can get this working.
Many thanks
john Caines

I'll try new code,, I've just seen it Krishnakumar,,,, many thanks,, I try now

 

[Krishnakumar]

Hi John,

Delete the module. Right click Sheet tab > View Code > paste the code there.

Now double click on any cells.

HTH

 

[Krishnakumar]

John,

The new code is not for you. You just take the first code.

 

[John Caines]

OOPs!!
Sorry Krishnakumar,,,

hey Krishnakumar,,, just seen your reply,,,,
"Delete the module. Right click Sheet tab > View Code > paste the code there.
Now double click on any cells."

COOL!!!!
I've never inserted a VBA code this way before,,,,,

Now I get the pop up if I double click on the spreadsheet,,, cool!!!
But,,,, it isn't clearing the rows of data

Not sure what is wrong,,,
I've PM'ed you Krishnakumar,, my spreadsheet,,,
Maybe it might help show what is wrong with it,,,
(Just PM it me back if you need to, don't really want it attached on the board,,

Again,, many thanks Krishnakumar,,,,
Getting there

 

[Dryver14]

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