Format excel data

[Paramasivan]

Hi All,

I am having the hierarchial data in the excel in the following format..
----------------------------------------------------------------------------------------------------------------------
Column A Col B Col C
Column D
----------------------------------------------------------------------------------------------------------------------
Airwick Freshmatic TOTAL Air Care Czech Republic
France
Germany
Great Britain
Greece
Hungary
Italy
Netherlands
Poland
Portugal
Airwick Mobil'Air COMPLETE Air Care Italy
Netherlands
TOTAL Air Care Czech Republic
Greece
Hungary
Portugal
----------------------------------------------------------------------------------------------------------------------


I need the output in the following format.. i.e the data need to be
updated from the cell
above which have the value..
----------------------------------------------------------------------------------------------------------------------
Col A Col B Col C
Column D
----------------------------------------------------------------------------------------------------------------------
Airwick Freshmatic TOTAL Air Care Czech Republic
Airwick Freshmatic TOTAL Air Care France
Airwick Freshmatic TOTAL Air Care Germany
Airwick Freshmatic TOTAL Air Care Great Britain
Airwick Freshmatic TOTAL Air Care Greece
Airwick Freshmatic TOTAL Air Care Hungary
Airwick Freshmatic TOTAL Air Care Italy
Airwick Freshmatic TOTAL Air Care Netherlands
Airwick Freshmatic TOTAL Air Care Poland
Airwick Freshmatic TOTAL Air Care Portugal
Airwick Mobil'Air COMPLETE Air Care Italy
Airwick Mobil'Air COMPLETE Air Care Netherlands
Airwick Mobil'Air TOTAL Air Care Czech Republic
Airwick Mobil'Air TOTAL Air Care Greece
Airwick Mobil'Air TOTAL Air Care Hungary
Airwick Mobil'Air TOTAL Air Care Portugal
----------------------------------------------------------------------------------------------------------------------

Can anyone please help me in writing some macro to get the desired
output.

Regards
Param

 

[Dave Peterson]

Debra Dalgleish has a manual method (quicker) and code at:
http://contextures.com/xlDataEntry02.html

 

[Gord Dibben]

Manually............

Select columns and F5>Special>Blanks>OK

Now in the active blank cell type an = sign.

Point to the cell above and CTRL + ENTER to fill down.

You can then copy and paste special(in place)>values>ok>esc to get rid of the
formulas.

VBA............

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long

Set wks = ActiveSheet
With wks
col = ActiveCell.Column
'or
'col = .range("b1").column

Set rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Cells(1, col).EntireColumn
.Value = .Value
End With

End With

End Sub


Gord Dibben MS Excel MVP

Hi All,

I am having the hierarchial data in the excel in the following format..
----------------------------------------------------------------------------------------------------------------------
Column A Col B Col C
Column D
----------------------------------------------------------------------------------------------------------------------
Airwick Freshmatic TOTAL Air Care Czech Republic
France
Germany
Great Britain
Greece
Hungary
Italy
Netherlands
Poland
Portugal
Airwick Mobil'Air COMPLETE Air Care Italy
Netherlands
TOTAL Air Care Czech Republic
Greece
Hungary
Portugal
----------------------------------------------------------------------------------------------------------------------


I need the output in the following format.. i.e the data need to be
updated from the cell
above which have the value..
----------------------------------------------------------------------------------------------------------------------
Col A Col B Col C
Column D
----------------------------------------------------------------------------------------------------------------------
Airwick Freshmatic TOTAL Air Care Czech Republic
Airwick Freshmatic TOTAL Air Care France
Airwick Freshmatic TOTAL Air Care Germany
Airwick Freshmatic TOTAL Air Care Great Britain
Airwick Freshmatic TOTAL Air Care Greece
Airwick Freshmatic TOTAL Air Care Hungary
Airwick Freshmatic TOTAL Air Care Italy
Airwick Freshmatic TOTAL Air Care Netherlands
Airwick Freshmatic TOTAL Air Care Poland
Airwick Freshmatic TOTAL Air Care Portugal
Airwick Mobil'Air COMPLETE Air Care Italy
Airwick Mobil'Air COMPLETE Air Care Netherlands
Airwick Mobil'Air TOTAL Air Care Czech Republic
Airwick Mobil'Air TOTAL Air Care Greece
Airwick Mobil'Air TOTAL Air Care Hungary
Airwick Mobil'Air TOTAL Air Care Portugal
----------------------------------------------------------------------------------------------------------------------

Can anyone please help me in writing some macro to get the desired
output.

Regards
Param

Gord Dibben MS Excel MVP

 

[Paramasivan]

Hi Dave,

Thanks. The VBA code snippet was really helpful.
I have got the desired output.

Thanks to the other guys who have responed to my query.

Regards
Param