Help debugging mostly working Macro - inserting rows

Hello everyone! I have a mostly working VBA Macro in Excel 2016 and I was hoping you all could help me find my error. To be honest this is my first serious attempt at a Macro. The situation is that I have a long list of text where Column A has many groups of cells with the same text. I'm trying to insert a row at the end of each group of the same text.

For context, this is a list of database tables with DB columns and other properties, and the first row is the Spreadsheet column names frozen at the top. The original list is just over 36,000 lines, and the code ends up inserting a little under 3,000 rows.

The problem is that the second code block below mostly works in that most groups of texts are successfully separated by a blank row, but sometimes a row is not inserted. I don't see a pattern between the number of correct insertions and the number of failed insertions. I'm assuming that I have a simple formatting or logic error that I am not seeing. Any thoughts? Any help is greatly appreciated!


This code works, but I wanted to be thorough and add a check for existing empty rows to prevent 40,000 contiguous rows from being inserted.

Please Login or Register  to view this content.

This is the new code that I need help with. I have made multiple changes to the code order, and separated the nested IF statements, but so far no luck.

Please Login or Register  to view this content.

Does the code just not do anything or give you an error?

If so after .insert you need shift:=xldown

could use xlup, xltoright or xltoleft as well I believe

As I mentioned the code works but sometimes does not insert rows. I'm guessing that roughly 1/2 of the needed rows are inserted. I don't get any errors when I run it.

I would try adding the shift as mentioned above but can you attach a sample so I can see what isn't happening for you

I made a small sample for myself to try and I came up with this code that appears to work. I used i instead of row1 just as a habit from how I was taught to code. I also changed it from 2 to 100 for simplicity sake but this should fix your problem.

Code Worked With this book

Please Login or Register  to view this content.

I appreciate the help, but unless I'm mistaken that looks like a re-work of my first code which I said was working. Did you look at the second code where I had added a condition for the detection of existing empty cells and skipping the empty cell?

you didn't specify the what the blank was looking for. My code is different and works however here is your code fixed.

I made the change to the checking for blanks

Please Login or Register  to view this content.

What I mean is you look for a blank but technically you are looking in the variable row1 not the sheet. The change now looks in the sheet.

Also as a side note to save on runtime since it runs through 40000 times I would recommend you put the row1=row1+1 in the if statement as I showed in the post using i values. This way the code won't check for a blank every single time but rather just skip over it each time it is added. Basically this takes running that code to roughly 3000 times (from number of rows you said are inserted above) as opposed to 40000.

My apologies I wasn't saying your description was unclear I was pointing out in your code you didn't specify that it was looking for a blank cell. I fully understood what you were looking for, rather I was explaining where your error was in your coding.

Also Please mark this forum as solved if I have fixed the issue. The big gurus appreciate that.

(To do this click on thread tools at the top of the forum and then select "mark forum as solved")