blank rows filling (Urgent)

[Guest]

Hi,
all

Here is a example.
I have three columns in excel.There are some blank cells and rows.
A B C
SMH SMHP CPR


MDR



SLK
CPD DHG STG
STG



Here is how it should look after filling up the blank columns
Col A Col B Col C
SMH SMHP CPR
SMH SMHP CPR
SMH SMHP MDR
SMH SMHP MDR
SMH SMHP MDR
SMH SMHP SLK
CPD DHG STG
CPD DHG STG


Right now I am doing it manually. Is there a way I can do it by writing some
VBA code or macros

Any help would be highly appreciated?

 

[Guest]

Try this:

Select your entire data range
<Edit><Go to>
Click the [Special Cells] button
Check: Blanks
Click the [OK] button

Then...while those blanks are selected...
Type =
then press the UP ARROW key one time
Then..holding down the [Ctrl] and [Shift] keys...press the [Enter] key

That should cause every blank cell to reflect the first available value
above it.

Note: to "hard code" those values...
Select the entire data range
<Edit><Copy>
<Edit><Paste Special>
Check: Values
Click the [OK] button

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Loly,

If you would like to work with macro:

Sub CopyInSelection()

'Macro fills in empty cells in the selection by copying each
'non-empty cell from upper left corner into all succeeding cell
'until coming across the next non-empty one. If the number of
'columns in the selection is greater or equal to the number of
'rows, the filling runs downwards, and vice versa.

'Petr Bezucha, 2005

Dim I As Integer, R1 As Integer, R2 As Integer
Dim J As Integer, C1 As Integer, C2 As Integer

With Selection
R1 = .Row
R2 = .Rows.Count
C1 = .Column
C2 = .Columns.Count
End With
S = ""
If R2 < C2 Then
For I = R1 To R2
For J = C1 To C2
GoSub Action
Next J
Next I
Else
For J = C1 To C1 + C2 - 1
For I = R1 To R1 + R2 - 1
GoSub Action
Next I
Next J
End If
Exit Sub
Action:
Set C = Cells(I, J)
If IsEmpty(C) Then
C.Value = S
Else
S = C.Value
End If
Return
End Sub

Regards

 

[Art Farrell]

Hi Kittie,

Another macro. Use a defined name for your range. I have used 'aces'as you
can see in the macro:

Sub Fillblanks()
Dim rng As Range

Application.ScreenUpdating = False
Set rng = Sheets("Sheet19").Range("aces")
rng.SpecialCells(xlBlanks) _
.FormulaR1C1 = "=R[-1]C"
rng.Copy
rng.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End Sub

CHORDially,
Art Farrell