Hi everyone,
I am using Excel 2007.
I don't have any programming experience and I have managed to create a macro that will do autofill once (with numerical increments of one). But now I need a macro that will:
Autofill series in increments of one
Work in a single column - with unknown number of rows
Will recommence the series from one - with an autofill from every time there is a value of '1' (however the '1' are at different distances apart
I found a code that has been fantastic for autofilling based on above value and wanted to adapt it for autofilling, but I really have no idea how to do it apart from autofilling from each instance of '1' in the column manually (potentially 100's of times).
The code I found for the fill above was by by Dave Peterson 2004-01-06
'fill blank cells in column with value above
From - http://www.contextures.com/xlDataEntry02.html
I thought this macro would be useful to try and adapt, changing the 'repeat above' values to 'autofill' when row value = 1 with autofill increments of 1.
Can anyone assist me to work out how I might do this?
Thanks in advance
I am using Excel 2007.
I don't have any programming experience and I have managed to create a macro that will do autofill once (with numerical increments of one). But now I need a macro that will:
Autofill series in increments of one
Work in a single column - with unknown number of rows
Will recommence the series from one - with an autofill from every time there is a value of '1' (however the '1' are at different distances apart
I found a code that has been fantastic for autofilling based on above value and wanted to adapt it for autofilling, but I really have no idea how to do it apart from autofilling from each instance of '1' in the column manually (potentially 100's of times).
The code I found for the fill above was by by Dave Peterson 2004-01-06
'fill blank cells in column with value above
From - http://www.contextures.com/xlDataEntry02.html
Code:
Sub FillColBlanks()
'[COLOR=Red]by Dave Peterson 2004-01-06[/COLOR]
'fill blank cells in column with value above
'http://www.contextures.com/xlDataEntry02.html
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 SubCan anyone assist me to work out how I might do this?
Thanks in advance
Last edited:
