You don't need a macro to do this.
Select the column and F5>Special>Blanks>OK
Enter an = sign in active cell and point or arrow to cell above.
CTRL + ENTER to fill.
Copy and Paste Special>Values>OK>Esc.
But here is a macro if you choose.
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.NumberFormat = "General"
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
trying to write a macro to do the following.
I have a column dates and blanks cells in between;
Nov 05
I would like a macro to autofill down until the next non blank cell is
found and then repeat the autofill with the new cell until the end of
the column ie final result.
Oct05
Oct05
Oct05
Nov05
Nov05
Nov05
Nov05
Dec05
Thanks in advance
Dave- Hide quoted text -
- Show quoted text -
Hi Dave. Try this. You have to input what cell you want to start
with and what row your last data is in.
Sub MacroCopy()
'
' Macro recorded 10/11/2007 by Dorothy Cooperson
'
Dim UserInput, UserInput2 As String
Application.ScreenUpdating = False
UserInput = InputBox("Please enter the cell you'd like to start
copying. e.g. A1", "Input")
UserInput2 = InputBox("Enter the last row that contains data.")
'Start with the first cell you want to copy and paste
Range(UserInput).Select
'Run this until you're at the last row that you input.
Do Until ActiveCell.Row = UserInput2
'Copy the data.
Selection.Copy
'Go down to the next row.
ActiveCell.Offset(1, 0).Select
'Go down until the next cell is populated.
Selection.End(xlDown).Select
'Go up one from the populated cell.
ActiveCell.Offset(-1, 0).Select
'Select up to the cell containing data.
Range(Selection, Selection.End(xlUp)).Select
'Paste the information in.
ActiveSheet.Paste
'Get out of the Cut/Copy mode.
Application.CutCopyMode = False
Selection.End(xlDown).Select
' ActiveCell.Offset(i, 0).Select
Loop
MsgBox "The cells are now copied."
Application.ScreenUpdating = True
End Sub
