Fill Macro

linesy

Board Regular
Joined
Sep 27, 2004
Messages
72
Hi,

I have a pivot table that I have copied, paste special value into another file so that I can sort a specific way. Column A has the Vendor, Column B the business unit. There are multiple business unit but the pivot shows the vendor once and then each of the business units. I need a macro to auto-fill the vendor inforomation in the left hand column (column A) for each line there is a business unit. Does anyone have any ideas??

Thanks.
Susan
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
if you select the blank cells in column A and insert the formula

=r[-1]c

Code:
Sub test()
With ActiveSheet.Columns(1)
  .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]c"
End With
End Sub
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
If your vendor is in cell A1, try:
Code:
Sub FillDown ()
Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row).FillDown
End Sub
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
A slightly smarter version

Code:
Sub fill2()
    With Range("A2", Cells(Rows.Count, "B").End(xlUp).Offset(, -1))
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]c"
        .Value = .Value
    End With
End Sub
 

linesy

Board Regular
Joined
Sep 27, 2004
Messages
72

ADVERTISEMENT

Weaver, worked perfectly. Thanks so much guys....you're great !!!
 

Ann99

New Member
Joined
Sep 14, 2009
Messages
27
Everyone, thanks, this is what I was looking for today!

I need a little more help with it.

My pivot table has 7 columns in the row area and the one necessary in the data area. The first 3 columns have blank rows under a row with a value. Can I use a macro to fill in the blanks with the value above in all three columns at once?

I did copy and paste the pivot table data onto a new worksheet, ran a macro to fill in col A, then copied and insered the other columns into column A to use the macro. It seems it should be easier.

Also a problem, on my trial worksheet, the "fill2" macro worked the first time, then after that only filled in the first blank under the topmost value. So I tried the "test" macro. Luckily it worked in mutiple trials. What could have happened with the "fill2" macro?

Thanks, Ann
 

Ann99

New Member
Joined
Sep 14, 2009
Messages
27
Hi, this is Ann
I take back some of my last post in this thread. I went back to my trial page and now both the Fill2 and Test macros are working:)! Don't know why the Fill2 didn't work before. But don't think any more about it.

I just need a macro, if possible, to fill in blank rows with a value in a row above in column A and also in columns B, C, etc at the same time. Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,460
Members
414,069
Latest member
StudExcel

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