My set of data looks like this:

VendorNbr Amount
1000 $900
$750
$500
1120 $600
1500 $700
$800

I want the data in a more "tabular" format, so I'm hoping to copy each
vendor number down to the next populated cell, and repeat the process all the
way down (about 9,000 rows). Doing this the manually for 9,000 rows isn't a
real option. Is there some code that could automate this?

http://j-walk.com/ss/excel/usertips/tip040.htm
Duplicate repeated entries in a list

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Kirk P."
wrote in message...
My set of data looks like this:

VendorNbr Amount
1000 $900
$750
$500
1120 $600
1500 $700
$800

I want the data in a more "tabular" format, so I'm hoping to copy each
vendor number down to the next populated cell, and repeat the process all the
way down (about 9,000 rows). Doing this the manually for 9,000 rows isn't a
real option. Is there some code that could automate this?

You could do it in code but there is a quick way to do it without coding.

Will assume your Vendor Nbr is in column A of your worksheet (YourSheet) and
has a column header in row 1.
Use another column - could be on the same sheet or another worksheet, even
another workbook. Just temporary anyway. I will use column C in this
example. In the 2nd row of this column put this formula:
=IF(A2="",C1,A2)
Use Autofill or Copy/Paste to duplicate this for the entire column. That
should fill in the proper numbers. Then copy the results, and use Paste
Special... Values to replace column A. Now you can delete the temporary
column.
--
- K Dales


"Kirk P." wrote:

My set of data looks like this:

VendorNbr Amount
1000 $900
$750
$500
1120 $600
1500 $700
$800

I want the data in a more "tabular" format, so I'm hoping to copy each
vendor number down to the next populated cell, and repeat the process all the
way down (about 9,000 rows). Doing this the manually for 9,000 rows isn't a
real option. Is there some code that could automate this?

Hi,


Sub copyDown()
Worksheets("sheet1").Select ' <==== change as required
With Worksheets("sheet1")
lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
For r = 3 To lastrow
If .Cells(r, "A") = "" Then .Cells(r, "A") = .Cells(r - 1, "A")
Next r
End With
End Sub

"Kirk P." wrote:

My set of data looks like this:

VendorNbr Amount
1000 $900
$750
$500
1120 $600
1500 $700
$800

I want the data in a more "tabular" format, so I'm hoping to copy each
vendor number down to the next populated cell, and repeat the process all the
way down (about 9,000 rows). Doing this the manually for 9,000 rows isn't a
real option. Is there some code that could automate this?

Works great. Thanks!

"K Dales" wrote:

You could do it in code but there is a quick way to do it without coding.

Will assume your Vendor Nbr is in column A of your worksheet (YourSheet) and
has a column header in row 1.
Use another column - could be on the same sheet or another worksheet, even
another workbook. Just temporary anyway. I will use column C in this
example. In the 2nd row of this column put this formula:
=IF(A2="",C1,A2)
Use Autofill or Copy/Paste to duplicate this for the entire column. That
should fill in the proper numbers. Then copy the results, and use Paste
Special... Values to replace column A. Now you can delete the temporary
column.
--
- K Dales


"Kirk P." wrote:

My set of data looks like this:

VendorNbr Amount
1000 $900
$750
$500
1120 $600
1500 $700
$800

I want the data in a more "tabular" format, so I'm hoping to copy each
vendor number down to the next populated cell, and repeat the process all the
way down (about 9,000 rows). Doing this the manually for 9,000 rows isn't a
real option. Is there some code that could automate this?