Microsoft Excel
--
Questions
--
Followers
Top Experts
Macro to copy and paste rows down to row above next entry
Hi - I hope someone can help me on this one, I need to do this exercise thousands of times!
I have a spreadsheet listing products. Each product has data in columns (columns A,B,C,D,E,F). Underneath each product are blank rows before the next product is listed.
I need a macro to copy Row1 columns A to F, and then paste the data in rows 2 to 5 (the next product on the list which is in Row6) - the macro then needs to continue that process until there are no products left.
The number of blank rows vary between each product, I have about six thousand products over about 20,000 lines
Any assistance would be really appreciated.
Many thanks
CopyDataSample.xlsx
I have a spreadsheet listing products. Each product has data in columns (columns A,B,C,D,E,F). Underneath each product are blank rows before the next product is listed.
I need a macro to copy Row1 columns A to F, and then paste the data in rows 2 to 5 (the next product on the list which is in Row6) - the macro then needs to continue that process until there are no products left.
The number of blank rows vary between each product, I have about six thousand products over about 20,000 lines
Any assistance would be really appreciated.
Many thanks
CopyDataSample.xlsx
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Go to your last row
Ctrl+Shift+Home to select all the data in your first columns
F5 \ Special \ Blanks to select all blank cells
Put in a formula the cell above the highlighted cells
=A2
Ctrl+Enter to put the formula in all the selected cells.
Select the columns,
Copy \ Paste Values
You're done.
Thomas
Ctrl+Shift+Home to select all the data in your first columns
F5 \ Special \ Blanks to select all blank cells
Put in a formula the cell above the highlighted cells
=A2
Ctrl+Enter to put the formula in all the selected cells.
Select the columns,
Copy \ Paste Values
You're done.
Thomas
Hi Thomas - Yes that is a neat solution but I forgot to mention that I do have some empty cells in the data that needs to be copied - so if I select 'blanks' it highlights the blank cells in the data rows too, so the formula = A2 doesn't work.
Is there a way that I can put a value in the blank cells in the data rows? Then they won't be empty and I can delete that value once all rows are copied.
I will upload a new visual and sample worksheet.
Many thanks
Is there a way that I can put a value in the blank cells in the data rows? Then they won't be empty and I can delete that value once all rows are copied.
I will upload a new visual and sample worksheet.
Many thanks
Here is an updated visual that shows that some of the data cells are empty so I can't use Thomas's suggested the blank cells option with the formula = A2
I've also uploaded an updated sample worksheet below
CopyDataSample.xlsx
I've also uploaded an updated sample worksheet below
CopyDataSample.xlsx
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Do an autofilter
Put a filter as Blank on your ID No.
Select only the visible Cells: Alt + ;
Now do the F5 \ Special Blanks \ Formulas \ Ctrl+Enter
remove the filter
copy paste values
Put a filter as Blank on your ID No.
Select only the visible Cells: Alt + ;
Now do the F5 \ Special Blanks \ Formulas \ Ctrl+Enter
remove the filter
copy paste values
Thanks for your replay but when I use the auto-filter the Special Blanks command still finds the blanks in the data missing on the product rows.
Have you selected only the visible cells before (Alt+;)
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
I must doing something wrong here when I hit Alt+; I get a row of =SUBTOTAL(9,) across one of the product rows.
Do the Alt+; on the attached file and see how it changes things.
CopyDataSample.xlsx
CopyDataSample.xlsx
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Try
Sub fillcurrentdata()
'Range("a3:G50").SpecialCells(xlCellTypeFormulas).ClearContents
ActiveSheet.Range("A:A").EntireColumn.Insert
With Range("A3:A" & Range("I" & Rows.Count).End(xlUp).Row)
.FormulaR1C1 = "=if(counta(RC[1]:RC[6])<2,"""",counta(RC[1]:RC[6]))"
.Value = .Value
For i = 1 To 6
With .SpecialCells(xlCellTypeBlanks).Offset(, i)
.NumberFormat = "general"
.FormulaR1C1 = "=R[-1]C"
End With
Next i
End With
ActiveSheet.Range("A:A").EntireColumn.Delete
End SubEARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
That's a brilliant solution Sulgurth and works really well on the sample file
The only thing is on the actual worksheet the data I need to copy and paste is in
columns B to I.
I was going to edit the macro myself but I don't feel confident to do so - could you please amend and re-post the updated macro.
Many thanks again for your help
The only thing is on the actual worksheet the data I need to copy and paste is in
columns B to I.
I was going to edit the macro myself but I don't feel confident to do so - could you please amend and re-post the updated macro.
Many thanks again for your help
to locate the column number, go into the spreadsheet, and in any cell in the column type "=Column()"
and the value is the column number.
To change the Fill range to B (Col 2) thru I (Col 9):
change the line that says for "J = 1 to 6" to J = 2 to 9
and you will need to change the control column that searches for data as well, that is the line that starts with "While"
Change the Cells(I, 8).value to Cells(I,ColumnNumber).valu
and the value is the column number.
To change the Fill range to B (Col 2) thru I (Col 9):
change the line that says for "J = 1 to 6" to J = 2 to 9
and you will need to change the control column that searches for data as well, that is the line that starts with "While"
Change the Cells(I, 8).value to Cells(I,ColumnNumber).valu
Many thanks Sulgurth that works a treat. And thanks for the additional explanation too for when the columns change etc.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Microsoft Excel
--
Questions
--
Followers
Top Experts
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.