Deleting rows and columns based off a macro..

[figuare9]

Okay, the title might suck, but I can't really think of a better one atm.. Little bit scatter brained trying to think of how to do what I'm looking for here.. lol.

So what I'm trying to do, is build a weekly planner based off a list of employees. Each day, the employee gets a few notes associated with what he did for that day. However, I'm trying to make it easy to remove and add employees. Which should work pretty well, because the list is sorted by seniority.

So if I add an employee, it will always be at the bottom of the list. However, if an employee leaves for any reason and I need to remove him, there's a very good chance that there will be people below him.

So, I have macros that were setup to add and remove employees in another thread I was helped with here.. https://www.mrexcel.com/forum/excel...help-specific-macro-maybe-better-use-vba.html

and after getting that working, I'd like to be able to have that employee list be the guide for each weekly planner. So, IE: if I remove an employee, I'd like to see him and all of his data in all of the sheets get removed. If that makes sense.. lol.

I built a working example sheet that can be found here.. http://www.mediafire.com/file/qisucr1bemd5y5d/Excel+Guide.xlsm Should help to understand better how it works.

I have a sheet for every week of the year. If I delete Eric, I'd like everybody and their data to go up one row to fill his spot. And new employees would be added on the bottom.

 

[Archangelos]

Let's assume that the employees' column is number 4 (or column D), the first employee row is 8 and the last one is 35.

I would recommend something like the following.

Dim Metritis as integer 'a counter
Dim FER as integer 'First Employee Row
Dim LER as integer 'Last Employee Row
Dim EmployeeToBeRemoved as string

FER=8
LER=35
EmployeeToBeRemoved="Eric"

For Metritis= FER to LER
      If Cells(Metritis, 4) = EmployeeToBeRemoved THEN
         Rows(Metritis).Delete
      End If
Next Metritis

 

[Archangelos]

What about deleting more than one at once? Let's see the sheet again.

Firstly, you mark the rows that must be removed.

Then run the following code.

Dim Metritis as integer 'a counterDim FER as integer 'First Employee Row
Dim LER as integer 'Last Employee Row


FER=8
LER=20


For Metritis= LER to FER STEP -1
      If Cells(Metritis, 4) = "Remove" THEN
         Rows(Metritis).Delete
      End If
Next Metritis

 

[Archangelos]

As you may have noticed, the For loop moves backwards, from bottom to top. THAT IS ESSENTIAL when deleting. Delete from bottom to top, delete from right to left.

Let's say that the For loop moved from top to bottom. When the counter (the integer Metritis) reaches row 11 it deletes John. By deleting John it's Nick who becomes row 11. However, the For loop cannot delete row 11 twice. When the For loop reaches row 11, it will delete John, Nick will move to row 11, Betty will move to row 12 etc. The first run will delete John, Kate, Stephanie but you will have to run the code for second time to have Nick removed.

The solution to that is to run the code backwards.

PS: the code I used to fill the employees' data is the following.
Sub Fill()
Dim Metritis As Integer
Dim AllosMetritis As Integer

For Metritis = 8 To 20
For AllosMetritis = 6 To 12
Cells(Metritis, AllosMetritis) = Cells(Metritis, 5) & " " & Cells(7, AllosMetritis) & " data"
Next AllosMetritis
Next Metritis
End Sub

 

[figuare9]

Wow. Very informative! Thank you very much good sir! Does this also work for every sheet the employee is in? I haven't got the chance to try this yet, just got back on my PC. It looks like I would need to change the name every time and run this code each time. I'm trying to build this entire thing with 2 buttons. My add and remove employee buttons. To make it as simple as possible for the end user.

Is there any way I can run it in the macro I already have setup? For example, when I remove an employee using the buttons I created in my sheet, could I also have it remove the employee from the weekly planner as well?

 

[figuare9]

Yeah, it's only barely making sense to me. Where you're declaring EmployeeToBeRemoved="Eric" I think that's where I need to call that in my existing macro where I remove the employee. Back to googling for me. . haha. Trying to get this to run off the same macro, instead of running it seperately.

 

[Archangelos]

Does this also work for every sheet the employee is in?

Well, the code needs an addition.

For example, you can make a sub named, for example, "RemoveEmployee" and then make a macro that calls all sheets and each time runs the "RemoveEmployee" macro.

Sub RemoveEmployee()    'Here goes the code to remove the employee
    'Perhaps code from the posts above
    'Or something else
End Sub




Sub PassThroughAllSheets()
Dim Fyllo As Worksheet
    
    
    For Each Fyllo In ThisWorkbook.Worksheets
             If Fyllo.Name = newSheet Then
                Call RemoveEmployee
             End If
    Next Fyllo
End Sub

I'm trying to build this entire thing with 2 buttons. My add and remove employee buttons.

I do not know this button thing. Do you mean a button on an Msgbox? Or something else?

Is there any way I can run it in the macro I already have setup? For example, when I remove an employee using the buttons I created in my sheet, could I also have it remove the employee from the weekly planner as well?

You can do many many fancy things, provided that you practise.

Like Samuel Beckett has said, Ever tried. Ever failed. No matter. Try Again. Fail again. Fail better..

Yeah, it's only barely making sense to me.

What do you mean? Is my code hard to understand.

Where you're declaring EmployeeToBeRemoved="Eric" I think that's where I need to call that in my existing macro where I remove the employee. Back to googling for me. . haha. Trying to get this to run off the same macro, instead of running it seperately.

I am not sure whether I understand what you mean.

 

[figuare9]

Did you happen to download the example I built? your code is fine btw!! Thank you for this.

In the Example Guide I built for this thread, found here http://www.mediafire.com/file/qisucr1bemd5y5d/Excel+Guide.xlsm on sheet1 (Employee List) there's two macro buttons. One is an add employee, and one is remove. If you click on remove employee, it will then ask you to click on the employee you want to remove. After clicking, you type yes, and it will kill that employee. I'm trying to have that macro remove the employee from "Employee List" Sheet, as well the weekly planner. So he's gone from the entire book.

Hopefully that helps to clear things up a bit.

 

[figuare9]

I'm still having some issues with this. Figured I'd try again after memorial weekend, but I'm still having unfortunately not able to figure this one out on my own.

 

[figuare9]

bump