Message box returning tasks completed at end of macro?

Question

I have a macro to remove specific undesired data from my spreadsheets data tab. I'm a major amateur with VBA so I'm sure my macro could probably be better but first it removes duplicates based on 3 criteria from the table, next it filters 3 columns and deletes every row of data based off said filters, clears all the filters and prompts a completed message box once the macro has finished running.

Is there any way to add to the message box or create a user form that will return the actions that were completed (# of duplicates removed, # rows deleted, etc)?

I know when you just use the duplicate removal function from the data tab it will alert to as to how many were removed and how many rows remain.

Here is my newbie script;

Sub Del_Dups_and_Remarks()
' Del_Dups

Application.ScreenUpdating = False
Range("DenialsTable1[[#Headers],[Payment Tx ID]]").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Range("DenialsTable1[#All]").RemoveDuplicates Columns:=Array(2, 6, _
        14), Header:=xlYes

' Remark_Removal

ActiveSheet.ListObjects("DenialsTable1").Range.AutoFilter Field:=19, _
        Criteria1:="=MEDICAID [239]", Operator:=xlOr

ActiveSheet.ListObjects("DenialsTable1").Range.AutoFilter Field:=22, _
        Criteria1:="Y"

ActiveSheet.ListObjects("DenialsTable1").Range.AutoFilter Field:=9, _
        Criteria1:="N598"
Application.DisplayAlerts = False
Range("DenialsTable1").Offset(1, 0).Select
Selection.Delete
Application.DisplayAlerts = True

'Clear_Filters

Range("A6").Select
ActiveSheet.ShowAllData
Range("A7").Select

Application.ScreenUpdating = True

MsgBox ("Completed")

End Sub

Thank you so much in advance!

Quick note, I highly recommend [avoiding using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) - it will help prevent headaches and unexpected behavior. — BruceWayne, Jul 18 '16 at 16:10
Thanks for the tip, what is my alternative, .activate? what sort of issue might I run into if I continue with the macro as is? Sorry if these are dumb questions but I appreciate any help you have time to give! — Awill, Jul 18 '16 at 16:36

SeanC · Accepted Answer · 2016-07-18T18:06:20.927

2

As it stands, ther are too many SELECTs, and the code will not delete the required criteria if it occurs in the first row

You are also missing Option Explicit - using this will alleviate many headaches in the future

Option Explicit

Sub Del_Dups_and_Remarks()
' Del_Dups
Dim InitCount as long
Dim AfterDups as long
Dim AfterDel as long

Application.ScreenUpdating = False

InitCount=Range("DenialsTable1").rows.count

ActiveSheet.Range("DenialsTable1").RemoveDuplicates Columns:=Array(2, 6, _
        14), Header:=xlYes
AfterDups=Range("DenialsTable1").rows.count
' Remark_Removal

ActiveSheet.ListObjects("DenialsTable1").Range.AutoFilter Field:=19, _
        Criteria1:="=MEDICAID [239]", Operator:=xlOr
ActiveSheet.ListObjects("DenialsTable1").Range.AutoFilter Field:=22, _
        Criteria1:="Y"
ActiveSheet.ListObjects("DenialsTable1").Range.AutoFilter Field:=9, _
        Criteria1:="N598"

Application.DisplayAlerts = False
ActiveSheet.Range("DenialsTable1").SpecialCells _
       (xlCellTypeVisible).Delete
Application.DisplayAlerts = True

AfterDel=Range("DenialsTable1").rows.count
'Clear_Filters

ActiveSheet.ListObjects("DenialsTable1").autofilter.ShowAlldata

Application.ScreenUpdating = True

MsgBox "Completed" & vbCrLf & "Initial Rows:" & InitCount & vbcrlf _
     & "Deduplicated " & InitCount - AfterDups & vbCrLf _
     & "Deleted " & AfterDups - AfterDel & vbCrLf _
     & "FinalCount " & AfterDel

End Sub

As pointed out by BruceWayne, it might be a little challenge for you to change the ActiveSheet to a variable, so that it will work from whatever sheet you run the code from

edited Jul 18 '16 at 18:06

answered Jul 18 '16 at 17:04

SeanC

15,695
5
45
66

1

I'd also add a note to set up a variable for the Worksheet, so you don't use `ActiveSheet`, in case that changes. – BruceWayne Jul 18 '16 at 17:07
This is fantastic thank you, the only thing I changed which may not even be the best way (but again newbie) was the delete command back to the format I had because it kept hitting an error if there were no rows to delete after filtering. Also, I'm not sure why but without separating the lines for filtering the criteria, it was filtering the wrong column. – Awill Jul 18 '16 at 17:59
@BruceWayne I added this macro to a button at the top of this worksheet to simplify it for the users, if the macro will only be ran when the user is currently on this sheet, is `ActiveSheet` fine to use in this case? – Awill Jul 18 '16 at 18:07
1

@awil, yes, that will be fine, but the reason for using an actual named sheet is because of the future... What happens if you have to do other things with the spreadsheet?... What about if your boss decides he wants them on a menu page? – SeanC Jul 18 '16 at 18:10
Yes, point taken. So I would just address the sheet (in this case named "Data") directly by substituting `Sheets ("Data")`? And admittedly I didn't even know what what it was or how to create a menu page in excel until I just looked it up, now I'm excited to take a crack at one. I've been trying to increase my knowledge with formulas and pivoting data fairly recently and exploring VBA a month or so, this has all been so helpful Thanks so much! – Awill Jul 18 '16 at 19:32
There are a few methods.... Substitute `Sheets("data")`... Use `with sheets("data")` then remove the word Activesheet, but leave the dot and everything else after... `dim ws as sheet ... set ws = Sheets("data")` then substitute `ws` for ActiveSheet – SeanC Jul 18 '16 at 21:54

Message box returning tasks completed at end of macro?

1 Answers1