the macro fill only the blanks cells, it did not insert any additional rows
as per my previous sample..

extracted column from original data:

Desc-1 Desc-2
Origin Bar Diameter
Emirates 8 mm
Oman 10 mm
12 mm

the results should show something like in the below,

Desc-1 Desc-2
Emirates 8 mm
Emirates 10 mm
Emirates 12 mm
Oman 8 mm
Oman 10 mm
Oman 12 mm

if you will noticed, it added 3 rows by repeating the diameter by its origin
and the word "origin" & "diameter" was deleted.


"Jacob Skaria" wrote:

Dear Eric

In the below macro please adjust the last row and last column as per your
requirement.

Sub Macro()
lngLastRow = 20
lngLastCol = 2

For lngRow = 2 To lngLastRow
For lngCol = 1 To lngLastCol
If Trim(Cells(lngRow, lngCol)) = "" Then
Cells(lngRow, lngCol) = Cells(lngRow - 1, lngCol)
End If
Next
Next

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"EricBB" wrote:

if you can notice the desc-1 & desc-2 columns below:
Desc-1 Desc-2
Origin Bar Diameter
Emirates 8 mm
Oman 10 mm
12 mm

it becomes,

Desc-1 Desc-2
Emirates 8 mm
Emirates 10 mm
Emirates 12 mm
Oman 8 mm
Oman 10 mm
Oman 12 mm

it added 3 rows by repeating the diameter by its origin. you can find those
in the "result sheet" from my uploaded file.

thanks for time.

"Jacob Skaria" wrote:

This should work if you select the Entire range of data before pressing (F5).

OR else

Try the macro

If this post helps click Yes
---------------
Jacob Skaria


"EricBB" wrote:

thank you for your reply. but what i want is to repeat the process as per the
number of items in desc-2.
below is the link to my file
http://www.savefile.com/files/2094166

"Jacob Skaria" wrote:

You can update this without using a macro.. Suppose column J is ununsed. Type
in a formula '= J1' in cell J2 and copy the cell.

Select your data range..Press F5 from 'Goto window' select Blanks. and OK.
This will select all blank cells. Right click.. Paste special..Formulas....

If this post helps click Yes
---------------
Jacob Skaria


"EricBB" wrote:

below is my original data:

ID Chap SubChap Class SubClass Unit Des1 Des2
2 Site
Subsurf
Subcon
Excavation LS
Backfill LS
3 Concrete
Formwork
Plywood
Marine pc Origin Thick
Brazil 4 mm
China 6 mm
Reinf.
Steel
Coated ton Origin Dia.
Qatar 4 mm
Oman 6 mm
8 mm
Uncoated ton Origin Dia.
Qatar 4 mm
Oman 6 mm
8 mm

what i want is a macro to make the above data same as below.

ID Chap SubChap Class SubClass Unit Des1 Des2
2 Site Subsurf Subcon Excavation LS
2 Site Subsurf Subcon Backfill LS
3 Concrete Formwork Plywood Marine piece Brazil 4 mm
3 Concrete Formwork Plywood Marine piece Brazil 6 mm
3 Concrete Formwork Plywood Marine piece China 4 mm
3 Concrete Formwork Plywood Marine piece China 6 mm
3 Concrete Reinf. Steel Coated ton Qatar 4 mm
3 Concrete Reinf. Steel Coated ton Qatar 6 mm
3 Concrete Reinf. Steel Coated ton Qatar 8 mm
3 Concrete Reinf. Steel Coated ton Oman 4 mm
3 Concrete Reinf. Steel Coated ton Oman 6 mm
3 Concrete Reinf. Steel Coated ton Oman 8 mm
3 Concrete Reinf. Steel Uncoated ton Qatar 4 mm
3 Concrete Reinf. Steel Uncoated ton Qatar 6 mm
3 Concrete Reinf. Steel Uncoated ton Qatar 8 mm
3 Concrete Reinf. Steel Uncoated ton Oman 4 mm
3 Concrete Reinf. Steel Uncoated ton Oman 6 mm
3 Concrete Reinf. Steel Uncoated ton Oman 8 mm

appreciate any help..