Macro to Delete Rows and Renumber Items after

Looking for a userform commandbutton macro "CommandButton1" to initiate a deletion of rows. Essentially "Combobox1" is a list of "Text" rows in columnA (after row16). I want the commandbutton to delete the "Text" row chosen in the combobox and all rows below it until the next "Text" row. After this I want all "Number" rows below the chosen "Text" row to be renumbered by subtracting "1.000". The sequence of order I guess doesn't really matter as long as the desired outcome is achieved (meaning if for vba purposes it is better to renumber all rows below the "Text" row chosen then delete the "Text" row and rows between next "Text" row that is perfectly fine)

Bump for help.....

Originally Posted by jaslake

Hi jared

Try replacing your UserForm7 Code with this

Please Login or Register  to view this content.

John it seemed to work fairly close to as intended. The only 2 minor adjustments that I would like to as to be made is:

1.) the first being if you look at cells A8:D15 there is a "Category" which matches the one being deleted. This is used to scroll through the worksheet. Along with deleting the rows I would as well like it to remove this "category" from the A8:D15 range and move those values up/across. If you look at the actual workbook you will see what I mean. One of the grey "Header" rows after row17 matches one of the grey "Categories" in A8:D15.

2.) The second being that after the user selects the item from the CombBox and hits commandbutton2 would it be plausible to reset the combobox as the Item that was deleted still remains in the combobox.

Thanks for your help

John,

One issue for undetermined reason it deletes the static text out of E10 and E11 when the code is ran?? Any suggestions? Other than that the code worked as intended

John,

I was doing some final testing and ran into a few issues. I have attached the newest revision of my workbook with your code. The only adjustment really is that the previously mentioned "Userform7" has now become "Userform05" I have added the adjustment to the code but ran into 2 problems. I have generated a new WS entitled "ARG-NEW AUDIT" just to test things on.

Problem1: Before running the code provided view another macro I have. This is worksheet code located on WS "ARG-130829" and WS "ARG-NEW AUDIT". How to test is hit a cell in the Categories "Box" (cells A8:D15) and if you watch it will scroll the matching row to the top of the page. When I run your macro it removes this feature both from the active WS but as well as the other WS. Kind of confused about this one???

Problem2: The second problem is that in the "Categories Box" (cells A8:D15) in a few trial attempts it did not adjust the categories over appropriately.

Attached Workbook: (PSM-14-02) PSAudit.xlsm

(PSM-14-02) PSAudit EXAMPLE.xlsm

John,

For Problem2 if it is not to much hassle....the goal would be for the "Cateogories Box" (A8:D15) to function as a whole so if you were to remove an item from the "A" side the "D" side would shift over accordingly. I have manually adjusted the categories box in the attached workbook to show an example. WS "ARG-130829" would be the original file and WS "ARG-NEW AUDIT" would be what it would look like after the category "Contractors" was deleted from cell A14. If you notice if there is items in D8:D15 they shift over accordingly. (I know this may seem weird but I plan on having a function to add as well so it will be importanat they are sequentially in order)

John,

Please don't feel any need to address right away at your convenience any help would be appreciated.

John,

Seems to work as intended (may I ask what causes the slight hesitation in the code)? As well you had mentioned adjusting the column width so you couldn't see the numbers? where might I do this at?

John,

Works beautifully and I was able to make the adjustments to the column width using the 1;0 setting in the properties. Thanks for all your help. If interested keep an eye out as I plan on making a post to do the opposite to add a category in between 2 categories (if you couldn't tell by the userform). Again I cant thank you enough.

Jared