How To Automate Copy & Paste....

Burrgogi

Burrgogi

Active Member
Joined
Nov 3, 2005
Messages
495
Office Version
  1. 2010
Platform
  1. Windows
In col. A, I have some "stuff"... mostly text.
In Col. B, I have this:
B1: Peaches
B2: --
B3: Oranges
B4: --
B5: --
B6: Nuts & Bolts
B7: Smith & Kline
B8: --
B9: Swimming Pool
B10: --
B11: --
B12: --
B13: --
B14: --


meaning that there is nothing contained in cell A15. So Loop, 'copy & paste' action as long as there is something in col. A.

dashes '--' denote empty cells.

Is there some function or VBA routine that can automatically fill in the empty cells so that for example in B2, it will show the value just above it - which in this case is Peaches. Copy & paste works fine, but the problem is that the number of empty cells (as you can see from the example here) varies. So if you look at the last item Swimming Pool, there are 5 empty rows compared to peaches which has only 1 empty cell. I'm trying to get the end result to look like this:

B1: Peaches
B2: Peaches
B3: Oranges
B4: Oranges
B5: Oranges
B6: Nuts & Bolts
B7: Smith & Kline
B8: Smith & Kline
B9: Swimming Pool
B10: Swimming Pool
B11: Swimming Pool
B12: Swimming Pool
B13: Swimming Pool
B14: Swimming Pool
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Try

Code: 
Sub FillBly()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B1:B" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub
 
Upvote 0
Sorry guys, but I need a little more help on this one. I changed the original worksheet so now that it has a column header in row 1. (Before, I had none). Now when I run the macro provided by VoG, it turns a =#REF! error each of the blank cells. What change(s) do I need to make to the code?
 
Upvote 0
Just change B1 to B2
Code: 
With Range("B1:B" & LR)
 
Upvote 0
For some reason the code provided by VoG keeps breaking. The last couple of times I ran the routine, it didn't work. Instead of the giving me the cell values, I got the following:

B1: Peaches
B2: =R[-1]C
B3: Oranges
B4: =R[-1]C
B5: =R[-1]C
B6: Nuts & Bolts
B7: Smith & Kline
B8: =R[-1]C
B9: Swimming Pool
B10: =R[-1]C
B11: =R[-1]C
B12: =R[-1]C
B13: =R[-1]C
B14: =R[-1]C
 
Upvote 0
Try

Code: 
Sub FillBly()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B2:B" & LR)
    .NumberFormat = "General"
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub
 
Upvote 0
VoG, I got an error message in VBE saying:

Run time '1004'
"No cells were found"
 
Upvote 0
Try selecting column B and run the TrimAll macro http://dmcritchie.mvps.org/excel/join.htm

Then run

Code: 
Sub FillBly()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B2:B" & LR)
    .NumberFormat = "General"
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    On Error GoTo 0
    .Value = .Value
End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
VBA sum pairs of cells from dynamic input list
Replies
3
Views
166
myall_blues
myall_blues
SanjayGMusafir
  • Solved
Text function not returning correct month
Replies
7
Views
245
Alex Blakenburg
A
S
  • Solved
A Different way to Calculate Ratios using GDC & or MIN ?
2
Replies
12
Views
585
StrawberryDreams
S
S
  • Locked
  • Question Question
A Different way to Calculate Ratios using GDC & or MIN ?
Replies
1
Views
260
Fluff
Fluff
W
  • Question Question
Copy formula across 28 day dynamic shift calendar
Replies
4
Views
347
Wolfgang17
W

Forum statistics

Threads
1,224,541
Messages
6,179,418
Members
452,912
Latest member
alicemil

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