Please try this ...

Sub Macro1()
For Each cell In ActiveSheet.UsedRange
If cell.Row 1 Then
Debug.Print cell.Address
If Trim(cell.Value) = "" Then cell.Value = Cells(cell.Row - 1,
cell.Column).Text
End If
Next
End Sub

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..