Hi all,

trying to write a macro to do the following.

I have a column dates and blanks cells in between;

Oct 05


Nov 05


Dec05

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

You don't need a macro to do this.

Select the column and F5SpecialBlanksOK

Enter an = sign in active cell and point or arrow to cell above.

CTRL + ENTER to fill.

Copy and Paste SpecialValuesOKEsc.

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

On Mon, 29 Oct 2007 15:13:38 -0700, Dave A wrote:

Hi all,

trying to write a macro to do the following.

I have a column dates and blanks cells in between;

Oct 05


Nov 05


Dec05

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

On Oct 29, 4:13 pm, Gord Dibben <gorddibbATshawDOTca wrote:
You don't need a macro to do this.

Select the column and F5SpecialBlanksOK

Enter an = sign in active cell and point or arrow to cell above.

CTRL + ENTER to fill.

Copy and Paste SpecialValuesOKEsc.

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



On Mon, 29 Oct 2007 15:13:38 -0700, Dave A wrote:
Hi all,

trying to write a macro to do the following.

I have a column dates and blanks cells in between;

Oct 05

Nov 05

Dec05

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