Auto Fill emtpy cell till next value with VBA

[winde]

Hi

Is there a code to do the following?

Before
A B C D
x x x x
x x x x
x x x x
E F G H
x x x x
x x x x
I J K L
xx x x

After
A B C D
A B C D
A B C D
A B C D
E F G H
E F G H
E F G H
I J K L
I J K L

I wan to fill the "x" which are empty.

 

[ravishankar]

try these codes

Sub jjj()
Dim a As Integer, b As Integer, x As Integer, y As Integer
x = Cells(Rows.Count, 1).End(xlUp).Row
y = Cells(1, Columns.Count).End(xlToLeft).Column
    For a = 1 To x
        For b = 1 To y
            If Cells(a, b) = "" Then
            Cells(a, b) = Cells(a - 1, b)
            End If
        Next b
    Next a
MsgBox "complete"
End Sub

ravishankar

 

[Fluff]

How about

Sub FillBlanks()
With ActiveSheet
   With Intersect(.UsedRange, .Range("A:D"))
      .SpecialCells(xlBlanks).Formula = "=r[-1]c"
      .Value = .Value
   End With
End With
End Sub

 

[winde]

try these codes

Sub jjj()
Dim a As Integer, b As Integer, x As Integer, y As Integer
x = Cells(Rows.Count, 1).End(xlUp).Row
y = Cells(1, Columns.Count).End(xlToLeft).Column
    For a = 1 To x
        For b = 1 To y
            If Cells(a, b) = "" Then
            Cells(a, b) = Cells(a - 1, b)
            End If
        Next b
    Next a
MsgBox "complete"
End Sub

ravishankar

Thanks ravi. How do i modify it if i wan it to be within a specific range and the number of rows to fill? As of now it will look for the next value but due to this, the last value doesnt get fill down since there is no next value.

 

[ravishankar]

try for a = 1 to x+1
ravi shankar