automatically insert row

[fishnfrogs]

Hello forum, I have a huge problem with Excel and I have no idea how to solve it. Hmm, how do I explain this? Someone is giving me data pulled from a server in an Excel format. I have a column that has a series of entries. For example:

Blank
Entry 1
Entry 2
Entry 3
Entry 4
Entry 5

However, sometimes there's an entry or two missing. So it'll look like this:

Blank
Entry 1
Entry 2
Entry 4
Entry 5

Is there anyway to tell Excel to say "Hey there's an entry missing here," and have it insert a row with the missing entry value? I do not work in Excel ever but people around here do, in case someone has some advice. I already asked the guy if he could just server me the data formatted correctly, and -- of course -- he can't/won't. Thanks in advanced!!

[shg]

Is the data as simple as that -- a single word followed by a single number?

[fishnfrogs]

Hi, it's more complicated than that -- I believe. I have every state in one column, then it has the group column that I'm having problems with. Then is one more column with a letter in it. After that there are a few more columns that have the actually data in them. So it really looks like this:

Alaska BLANK A 75 more data columns etc
Alaska GROUP01 A 74 more data columns etc
Alaska GROUP02 A 74 more data columns etc

I'm attaching a worksheet that might help explain my situation a little better. I pretty much need it to look at the 'Group' column and determine that there is a value missing and insert a row. If it could even fill in the values of the other columns that would be amazing. Thanks for your help!!

[shg]

Again -- is the data in column B as simple as that? Or exactly like that: GROUP followed by a 2-digit number?

[fishnfrogs]

It's just like GROUPXX.

[shg]

Try this:

Please Login or Register  to view this content.

Adding a Macro to a Code Module
1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Close the VBE to return to Excel

Running a Macro
1. Do Alt+F8 to open the macro dialog
2. Select the macro name (here, GroupXX) from the dropdown list and press Run

[fishnfrogs]

Hi, thanks for that. It's mostly working now. However, can you think of a way to insert the rows after a cell in the column is blank and the next cell value is GROUP03? If not, I can live with this. You're a life saver. Thanks!!

[shg]

Maybe if you post before and after examples, showing (in the after example) the inserted rows highlighted.

[fishnfrogs]

Hmm...I haven't been able to figure anything out. Well, actually, your code worked perfectly. However, maybe to make things simpler, would you know of a way to insert a new row after a blank row and if the next row isn't GROUP00. That way I could run that script, then the script you gave me, and it should fill in all the missing values for me. I've tried using this:

Please Login or Register  to view this content.

But it inserts the row above the blank one. I wanted it to go below. Any suggestions are appreciated. Thanks again!!

[shg]

Please provide before and after examples of what you want.

[fishnfrogs]

Hi, here are the examples. Thanks!!

[Leith Ross]

Hello fishnfrogs,

Here is another macro to add in the missing rows with the group and sequence numbers. I have run this on the data you posted and it works. Let me know if I missed anything. This is little more complex than Shg's code, so I haven't added comments to explain what is happening. The complexity makes it a little more difficult to understand, but also provides a lot of flexibility in string manipulation.

I see from your profile you're a Tar Heel. I grew up in in the western side of the state in Asheville. Where are you?

Macro Code

Please Login or Register  to view this content.