Macro to Add and Delete Row (by unhiding and hiding)

[Mo_Blues]

Hi all

I have a table with resource names and billable hours by week. I want to add the capability for users to add and delete rows. I want to do this by hiding and unhiding the rows the user can see. I have a flag indicator (column B in the sample file) that equals 1 if the row is visible and 0 if the row is hidden (I already have some code that will to do this part).

What I need to do is add another couple of macros to:

1. Add a new row by going through the values in B5:B15 to find the first 0 and change it to a 1.

2. Delete a row by going through the values in B5:B15 to find the last 1 and changing it to a zero AND clearing the corresponding row cells in columns E to J (E7:J7 in this example).

Can anyone help out with?

Hugely appreciated.

Thanks,
Mo

[Greg M]

Hi there,

The following code should do what you need:

Please Login or Register  to view this content.

The highlighted values may be changed to suit future workbook/worksheet layout changes.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[Mo_Blues]

Hi Greg,

This works great as long as it's in Sheet1. If I try to apply the code in my model where it's not in sheet 1 I get a "subscript out of range" error. I've tried changing the second line of the code (const msSHEET_NAME) to the correct sheet and I still get the error. Is there any other place i need to update the sheet number?

Thanks a lot!
Mo

[Greg M]

Hi again,

There are two ways to approach this.

The cheap 'n' cheerful (and risky!) way is to change the function as follows:

Please Login or Register  to view this content.

The risk is that you may accidentally run the application when an unintended worksheet happens to be active and you end up deleting important data.



The more complicated (but safer) way is to define an array of worksheets on which you intend the application to operate:

Please Login or Register  to view this content.

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M