Remove an associate via MSGBOX

[Excel Ent]

Good day
I am working on a vacation tracking workbook that references one input page across multiple tabs. Once the user enters the associate's name and qualifications in the Input tab, the Jan-Dec tabs populate with this information. What I am trying to do is to offer the user an option to remove an associate if they resign or retire via a message box. I can get this to work however the results are not as expected. The associate's name is removed from the Inputs tab as well as the monthly tabs however when it is sorted, it puts a 0 in the top cell A3 on the inputs tab and the header on the Jan tab is missing. Any help would be appreciated.


Sub Remove_AN_ASSOCIATE()
Dim MESSAGE As String
Dim RNG As Range

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("Inputs").Select
Let MESSAGE = InputBox("Enter the associate.", "Associate Removal Form")
If MESSAGE = vbNullString Then Exit Sub

For Each RNG In Range("A3:M358")
If RNG = MESSAGE Then RNG.ClearContents
Next
Range("A3:M358").Sort Key1:=Range("A3:M358"), Order1:=xlAscending, Header:=xlNo

Sheets("Jan").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo

Sheets("Feb").Select
For Each RNG In Range("A3:AD358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AD358").Sort Key1:=Range("A3:AD358"), Order1:=xlAscending, Header:=xlNo

Sheets("Mar").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo

Sheets("Apr").Select
For Each RNG In Range("A3:AF358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AF358").Sort Key1:=Range("A3:AF358"), Order1:=xlAscending, Header:=xlNo

Sheets("May").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo

Sheets("Jun").Select
For Each RNG In Range("A3:AF358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AF358").Sort Key1:=Range("A3:AF358"), Order1:=xlAscending, Header:=xlNo

Sheets("Jul").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo

Sheets("Aug").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo

Sheets("Sep").Select
For Each RNG In Range("A3:AF358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AF358").Sort Key1:=Range("A3:AF358"), Order1:=xlAscending, Header:=xlNo

Sheets("Oct").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo

Sheets("Nov").Select
For Each RNG In Range("A3:AF358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AF358").Sort Key1:=Range("A3:AF358"), Order1:=xlAscending, Header:=xlNo

Sheets("Dec").Select
For Each RNG In Range("A3:AG358")
If RNG = MESSAGE Then RNG.SpecialCells(xlCellTypeConstants).ClearContents
Next
Range("A3:AG358").Sort Key1:=Range("A3:AG358"), Order1:=xlAscending, Header:=xlNo

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[6StringJazzer]

You haven't given any description of what your data looks like. I am going to assume that the person's name is in column A, and you want to delete the whole row.

First, your code checks every cell on every sheet for the name. This takes a very long time, and you just need to look in one column. Second, you are only deleting constants. Without any explanation of your file, the reason for this is a mystery.

I would do this. This is taken from Jan but applies to all:

For Each RNG In Range("A3:AG358")
   If RNG = MESSAGE Then RNG.EntireRow.Delete
Next RNG

 

[Joe4]

You haven't given any description of what your data looks like. I am going to assume that the person's name is in column A, and you want to delete the whole row.

First, your code checks every cell on every sheet for the name. This takes a very long time, and you just need to look in one column. Second, you are only deleting constants. Without any explanation of your file, the reason for this is a mystery.

I would do this. This is taken from Jan but applies to all:

For Each RNG In Range("A3:AG358")
   If RNG = MESSAGE Then RNG.EntireRow.Delete
Next RNG

A few issues there.

1. If the person's name is in column A (like you assume), the range you want to loop through should be "A3:A358", not "A3:AG358".
Otherwise, you code is unnecessarily check through an additional 32 columns (columns B:AG)!

2. When deleting rows, using the technique you list could miss records if there are two or more consecutive rows that need to be deleted. The reason is because you are shrinking the size of the range as you are working your way down it. Let's say that you had three records, in A1, A2, and A3, and A1 and A2 meet your criteria for deletion.
Working through the loops, it would look like this:

Loop 1:
Cell A1 checked.
Row 1 deleted.
A2 shifts up to A1, and A3 shifts up to A2.

Loop 2:
Cell A2 checked.
No row deleted (because the row you actually wanted to check moved up from A2 and A1, and therefore gets "skipped" over.

Loop 3:
Nothing to check, as no data currently resides in A3.

So when looping through data to delete rows of data, you have to take a different approach. Either:
1. Start at the last row of data and work your way up, so no rows of data will get skipped over.
2. Do not delete the data as you loop through it. Just mark it for deletion, and then delete all rows at once at the end.

I prefer method 1, and that would look something like this:

Dim rw as Long
For rw = 358 to 3 Step -1
    If Cells(rw, "A") = "MESSAGE" Then Rows(rw).Delete
Next rw

Note that if each person can only appear once on each sheet, then this wouldn't be a concern, and you do not need to worry about this.
But if they can, then you will need to do something like this.

 

[Joe4]

Going back to the original question, it would be helpful to see what your data looks like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also, you can loop through your sheets, so you don't need to repeat your code 12 times. We would just need to know what other sheets appear in this workbook, other than these 12, so we know how to write the loop to exclude those. So it would be helpful to know those sheets' names.

 

[Excel Ent]

Inputs tab below (range is "A3:M358") don't want to delete entire row, just a-m of user input of associate.

JAN tab pulls name and schedule from Inputs. When user enters associate to remove in Inputs tab, it removes it from all other tabs Jan-Dec (ranges vary)
Inputs range is A3 thru M358
Jan is A3 thru AG 358 etc.

 

[6StringJazzer]

1. If the person's name is in column A (like you assume), the range you want to loop through should be "A3:A358", not "A3:AG358".
Otherwise, you code is unnecessarily check through an additional 32 columns (columns B:AG)!

Absolutely correct. I made this point but did not properly update the code to deal with it.

2. When deleting rows, using the technique you list could miss records

Also correct. Rookie mistake. However, based on the OP's subsequent post, the row is not to be deleted so this will not come into play.

 

[Excel Ent]

In response to above photos. When the user clicks on the remove associate button (not shown), the message box takes the user input, finds it in Inputs tab(left picture) and clears columns A thru M. So if the user enters Cobra on the Inputs tab, it will clear cells A8 thru M8 on the Inputs tab. In the Jan tab (right picture) I need the name removed and all of the "P"'s, so if coded properly, when the user removes Cobra from the Inputs tab, cells A8 thru AG8 will be cleared on the Jan tab.