copy & paste multiples

danalee

New Member
Joined
Apr 20, 2009
Messages
26
Good morning,

I have a spreadsheet with over 20000 rows.
Scattered among the rows (in no particular pattern) are empty rows.
I need to know if there is a way to have excel look for the empty rows then "copy" the row above the empty row and "paste" into the empty row.

This would be a major help for a project that will be ongoing for years.

Thank you!
DanaLee
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
If you're willing to use helper columns, there is a relatively straightforward way, assuming you only need to do this once.

In the helper column, put something like this on row 2
Code:
=if(A2="",+A1,+A2)
and copy down as far as required.
Repeat this for as many columns of data as you actually have.
Once finished, you can use Copy, Paste Special, Values, to "freeze" the result of this formula.
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,478
Where you do have non-empty rows, are all fields (col A-K, say) non-empty cells?

If so then I'd suggest Highlighting the entire Range;
Get to the Goto Special... Dialog and select BLANKS
At your activecell enter = (then arrow up to the cell just above it) and PRESS CONTROL+Enter (not just enter).
Finally, With Full Range Selected - do a copy pastespecial values to remove formulas
created above.
DONE!!
 

danalee

New Member
Joined
Apr 20, 2009
Messages
26
Oh My Gosh!

I wasn't sure it could be done but I knew where to come to ask. This will save so much time for me and my co-workers.

Thank you - Thank you - Thank you

That was from them also


*smile*
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top