Copy data into new sheet with a break after every nth row or copy formula into every cell with breaks every nth row


Disclaimer

Hello. If you have the answer to your r/Excel question, please reply to the answer with Solution Verified to award a Clippy Point and Clippy will set the question flair to Solved for you.

Thank you


With a tiny bit of VBA you can do this as quickly as you can run it.

Sub Add_Blank_and_Color()
For rw = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Range("A" & rw) <> Range("A" & rw + 1) Then
Rows(rw + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A" & rw + 1 & ":F" & rw + 1).Interior.ColorIndex = 37
rw = rw + 1
End If
Next rw
End Sub

Link to the VBA color pallet


To access the VBA Editor , Insert (or) Work with a Module and Code it:

  • Press Alt & F11 keys. In the left panel, right click and select Insert -> Module (or) Double Click the pre existing module to work with.

  • For accuracy, double click on the module in the list.

  • Left click inside the Right panel and paste the code.


Have a great day!! :-)

/r/excel Thread