Rob_010101
Board Regular
- Joined
- Jul 24, 2017
- Messages
- 198
- Office Version
- 365
- Platform
- Windows
Hello,
The below macro is assigned to a button on the ribbon and works on the "current 6 months" sheet. It looks down column B for "terminated" and deletes the row. It's currently restricted to only work on "current 6 months" sheet.
Is there any way to add another sheet "Previous 6 months" to it, so it will work on both sheets only?
Thanks in advance
The below macro is assigned to a button on the ribbon and works on the "current 6 months" sheet. It looks down column B for "terminated" and deletes the row. It's currently restricted to only work on "current 6 months" sheet.
Is there any way to add another sheet "Previous 6 months" to it, so it will work on both sheets only?
VBA Code:
Sub deleteTerminatedEmployees() 'v1b
Range("A1").Select
Selection.End(xlUp).Select
Range("A1").Select
'****************************************
'CHECK FOR APPROPRIATE WORKSHEET..
'****************************************
zSheet = "Current 6 Months" '<< specify required sheet tabname
If ActiveSheet.Name <> zSheet Then 'on wrong sheet!
saywhat = "This routine only works on sheet tabs [Current 6 Months]." 'message txt
saywhat = saywhat & vbCr & vbCr
saywhat = saywhat & "Switch to correct sheet and try again!"
boxtitle = "Employment Status" 'message box heading
btns = vbOKOnly + vbExclamation 'message box buttons
answer = MsgBox(saywhat, btns, boxtitle) 'display message box
Exit Sub 'nothing else to do
End If 'end of test for a valid worksheet
'****************************************
'Worksheet is appropriate, so continue here..
Set zBlock = [a1].CurrentRegion 'set shortcut for data block
'set filter for column B.. 'for Field, col A = 1, col B = 2 etc
zBlock.AutoFilter Field:=2, Criteria1:="Terminated" 'set required filter for column 2
zCount = zBlock.Columns(1). _
SpecialCells(xlCellTypeVisible).Count - 1 'filtered records; -1=ignore header row
If zCount = 0 Then 'no matches for this filter
saywhat = "There are NO records in the filtered block for status Terminated!" 'message txt
boxtitle = "Employment Status" 'message box heading
btns = vbOKOnly + vbExclamation 'message box buttons
answer = MsgBox(saywhat, btns, boxtitle) 'display message box
zBlock.AutoFilter Field:=2 'cancel the 'Terminated' filter
Exit Sub 'nothing else to do
End If 'end of test for NO status Terminated
'There are filtered records for status Terminated, so request confirmation before deleting..
If zCount = 1 Then
saywhat = "There is ONE record in the filtered block for status Terminated!" 'message txt
Else 'otherwise..
saywhat = "There are " & zCount & " records in the filtered block for status Terminated!"
End If 'end of check for number of records
saywhat = saywhat & vbCr & vbCr 'add 2 lines to message text
saywhat = saywhat & "Do you want to delete these?" 'add text
btns = vbYesNoCancel + vbDefaultButton2 'message box buttons; set default to btn2
answer = MsgBox(saywhat, btns, boxtitle) 'display message box
If answer <> vbYes Then 'User did NOT click [Yes] button, so..
zBlock.AutoFilter Field:=2 'cancel the 'Terminated' filter
Exit Sub 'nothing else to do
End If
'User has confirmed [Yes] to delete the filtered records, so..
zBlock.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
zBlock.AutoFilter Field:=2 'cancel the 'Terminated' filter
End Sub
Thanks in advance