Macro for Auto Fill
You don't need a macro for this.
Select Column A and F5SpecialBlanksOK
Type an = sign in active cell then point to cell above.
Hit CTRL + ENTER
Copy then paste special(in place)ValuesOKEsc
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
On Tue, 6 Dec 2005 11:55:02 -0800, "carolini"
wrote:
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
|