Copy and Paste Row 1:1 into Blank Rows

[Caraexcel]

Good Morning,

I have basic VBA Skills. My spreadsheet has a lot of rows and columns with row G with address numbers. I am using the following code to enter a blank line before each number that doesn't match the one below it For example:
234
234
235
236
236
becomes:
234
234

235

236
236

Please Login or Register  to view this content.

I also need to copy and paste row 1 into each of the newly created blank rows. I want to copy and paste so the format will be maintained. I have this below but I have to manually enter each row to enter it which defeats the purpose of automating.

Please Login or Register  to view this content.

I would like this to be combined into one single code and have it auto fill.

Thank you for your help in advance.

[patel45]

can you attach a sample file with data and desired result ?

[Caraexcel]

Please see attached. Thank you very much. I hope I attached it correctly, please let me know if you can't get it. I had to change the information to basic info but it should be ok for what is needed.

Thank you

[patel45]

I asked for desired result

[Caraexcel]

I apologize, I want the first row copied and inserted below a row where the address numbers are not the same. I attached the same spreadsheet but with the results I want. This is a much smaller version of the entire file.

[HaHoBe]

Hi, Caraexcel,

Your opening post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

[Caraexcel]

Hello,

Thank you I think I have it now?

Thank you,
Cara

[HaHoBe]

Hi, Cara,

the worst thing to do is start with VBA and use MergedCells. Get rid of these as they will only bloat the code with unnecessary attributes in order to perfom.

Run this code on a copy of the worksheet

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Ciao,
Holger

[Caraexcel]

Holger,

It worked perfectly, over 26,000 rows so it took a while but I honestly cannot tell you how much I appreciate this, 3 days pulling my hair out and you did it in just a short while, you are wonderful!

Great first time experience with this forum!

THANK YOU!
Cara

[HaHoBe]

Hi, Cara,

if everything is working like you want please consider to mark this thread as Solved via Thread-Tools over the opening post.

Ciao,
Holger