View Full Version : Solved: button for delete entire data in worksheet
DarReNz
11-14-2005, 09:37 PM
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
11-14-2005, 09:53 PM
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
11-14-2005, 10:22 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.