Solved: button for delete entire data in worksheet

[DarReNz]

Hi, I have a button in a worksheet and when pressed it ask for a confirmation and if Yes it deletes all the data from row 3 onwards. How do i do this ? Thanks

[malik641]

Hi DarRenz :hi: Welcome to the Forums!

Double click the button and place this code in the _Click procedure:
Option Explicit

Private Sub CommandButton1_Click()
Dim iLastRow As Long
Dim iLastCol As Long
Dim i As Long

If MsgBox("You are about to delete all rows from 3 down, are you sure?", vbYesNo) = vbNo Then Exit Sub

iLastRow = 1
iLastCol = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column

For i = 1 To iLastCol Step 1
If ActiveSheet.Cells(Rows.Count, iLastCol).End(xlUp).Row > iLastRow Then
iLastRow = ActiveSheet.Cells(Rows.Count, iLastCol).End(xlUp).Row
End If
Next i

ActiveSheet.Range(Cells(3, 1).Address, Cells(iLastRow, iLastCol).Address).EntireRow.Delete
End Sub
I tested it out and it works for me pretty well :thumb
Enjoy!

[johnske]

If it's a button created from the 'Forms' menu, assign this macro to it
Sub ClearRows()
Dim Confirm As VbMsgBoxResult
Confirm = MsgBox("You are about to delete the contents of all" & vbNewLine & _
"rows from row 4 down, are you sure?", vbYesNo, "Delete Rows?")
If Confirm = vbNo Then Exit Sub
Range(Cells(4, 1), Cells(Rows.Count, Columns.Count)).ClearContents
End Sub