Unmerge, then copying to blank cells

C

CaboRobbo

New Member
Joined
Jul 7, 2011
Messages
39
I have about 30,000 rows of data with merged cells. I have unmerged all of the cells to create a pivot table, and have many resulting blank cells.

I need any easy way to copy data from the populated cells down to the blank cells, on a large scale since 30,000 rows. Example: copy data from A5 to A6:11, data from A12 to A13:a20, data from A21 to A22:A30, etc.

Thanks in advance for any ideas.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Select Range you want to Fill
Edit | Goto… In 2007 hit Alt-E, G
Special…
Blanks | OK
Hit the "=" key. Hit the Up arrow. Hold down Ctrl and hit Enter.
(you now have a formula in each blank cell)
Select Range again, not just blank cell range.
Crl-C to copy
Edit | Paste Special | Values… In 2007 Click Paste Icon in the Home Ribbon, Paste Special, then click "Values".
(you will now have the values in each former blank cell)
 
Upvote 0
I have a similar issue - I use subtotals and need the data in col A to paste down to the blank cell below that contains the subtotal. I tried to use a macro but don't know VBA and can't figure out how to make it repeat down to the next blank cell. I manually do this by hitting :

<END key>down arrow
ctrl +c
down arrow <ENTER key>

<TABLE style="WIDTH: 163pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=217 border=0><COLGROUP><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; WIDTH: 32pt; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 15pt; BACKGROUND-COLOR: silver" width=42 height=20>Unit</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=75>Rpt Entity</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; WIDTH: 75pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=100>Sum Amount</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>10000</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">1000000</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>-45,000.00</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 1" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2>1000000 Total</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>10000</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2000035</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>-2,793,217.00</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>10000</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2000035</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>0.00</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>10000</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2000035</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>295,834.15</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 1" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2>2000035 Total</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>20114</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2011401</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>-267.80</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>20114</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2011401</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>133.91</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>20114</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2011401</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>0.00</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>20114</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2011401</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>-3,472,842.03</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>20114</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2011401</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>155,107.96</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>20114</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2011401</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>-538,468.00</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>20114</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2011401</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>-30.00</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 1" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2>2011401 Total</TD></TR></TBODY></TABLE>


any help is appreciated - this is a tedious repetitive procedure and would think a macro should be able to automate the repetitive key strokes - and I use Excel 2010

thanks,
LF
 
Last edited:
Upvote 0
Hi John (Datsmart),

I have a similar issue - I use subtotals and need the data in col A to paste down to the blank cell below that contains the subtotal. I tried to use a macro but don't know VBA and can't figure out how to make it repeat down to the next blank cell. I manually do this by hitting :

<END key>down arrow
ctrl +c
down arrow <ENTER key>

<TABLE style="WIDTH: 163pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=217 border=0><COLGROUP><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; WIDTH: 32pt; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 15pt; BACKGROUND-COLOR: silver" width=42 height=20>Unit</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT-COLOR: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=75>Rpt Entity</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT-COLOR: windowtext; WIDTH: 75pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=100>Sum Amount</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20>10000</TD><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8">1000000</TD><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right>-45,000.00</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 1" height=20><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20></TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8; mso-ignore: colspan" colSpan=2>1000000 Total</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20>10000</TD><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8">2000035</TD><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right>-2,793,217.00</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20>10000</TD><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8">2000035</TD><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right>0.00</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20>10000</TD><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8">2000035</TD><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right>295,834.15</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 1" height=20><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20></TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8; mso-ignore: colspan" colSpan=2>2000035 Total</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20>20114</TD><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8">2011401</TD><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right>-267.80</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20>20114</TD><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8">2011401</TD><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right>133.91</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20>20114</TD><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8">2011401</TD><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right>0.00</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20>20114</TD><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8">2011401</TD><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right>-3,472,842.03</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20>20114</TD><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8">2011401</TD><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right>155,107.96</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20>20114</TD><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8">2011401</TD><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right>-538,468.00</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 2" height=20><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20>20114</TD><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8">2011401</TD><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right>-30.00</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 1" height=20><TD class=xl64 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=20></TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8; mso-ignore: colspan" colSpan=2>2011401 Total</TD></TR></TBODY></TABLE>
any help is appreciated - this is a tedious repetitive procedure and would think a macro should be able to automate the repetitive key strokes - and I use Excel 2010

thanks,
LF
<!-- / message --><!-- edit note -->
 
Upvote 0
The same suggestion will work for your data.

Select the Range you want to fill in Column A.
On the Home tab, in the Editing group, click Find & Select, and then click Go To.
(Keyboard shortcut*for Go To is CTRL+G)
Click Special…
Blanks | OK
Hit the "=" key. Hit the Up arrow. Hold down Ctrl and hit Enter.
(you now have a formula in each blank cell)
Select Range again, not just blank cell range.
Crl-C to copy
In the Home tab, Click Paste Icon dropdown arrow
Click Paste Values
(you will now have the values in each former blank cell)

Here is the same procedure in VBA code:
Code: 
Sub AutoFill2()
Dim rng As Range
Dim c As Range
Dim lr As Integer
    lr = Cells(Rows.Count, 2).End(xlUp).Row
'Assign Formula in Range
    Range("A1:A" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
'Assign Range to work with
    Set rng = Range("A1:A" & lr)
'Assign Value inplace of formulas
    For Each c In rng
        c.Value = c.Value
    Next c
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA code to fill in blank cells running really slowly
Replies
6
Views
366
Joe4
Joe4
A
  • Question Question
Copy data from unmerged to merged cells
Replies
1
Views
436
6StringJazzer
6StringJazzer
C
  • Solved
Automatically refresh pivot table filter to remove spaces and blanks
2
Replies
15
Views
439
Alex Blakenburg
A
F
  • Question Question
Copy (=) data from a merged cell into an unmerged cell
Replies
2
Views
405
FireBott
F
M
  • Question Question
Remove blank cell of rows and shift the rest to left
2 3 4
Replies
31
Views
840
mehrana
M

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

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
Back
Top