Macro for Auto Fill

[Guest]

Can anyone tell me how to write a macro that will allow me to autofill a
column that has mulitple entries: Thanks!!1

Column A
Row 1 Sample
Row 2 Auto Fill w/ Sample
Row 3 Auto Fill w/ Sample
Row 4 Data
Row 5 Auto Fill w/ Data
Row 6 Auto Fill w/ Data

 

[Gord Dibben]

You don't need a macro for this.

Select Column A and F5>Special>Blanks>OK

Type an = sign in active cell then point to cell above.

Hit CTRL + ENTER

Copy then paste special(in place)>Values>OK>Esc

If you prefer a macro.......

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 Excel MVP

 

[Guest]

Thanks!!! This was very helpful!!!!