Fill Blanks Macro

[Guest]

I have a spreadsheet that has 44,383 rows. I am trying to use the following
macro to fill in the blanks for columns A. I am getting a REF error. I
know it is the number of rows because the macro works for spreadsheets that
do not have as many rows. Is there any modification to this code that will
make the macro fill down for 44,383 rows:

Sub FillBlanks2()
Dim Rng As Range
On Error Resume Next
Set Rng = ActiveSheet.UsedRange.Columns("A")
With Rng
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Copy
.PasteSpecial xlPasteValues
End With
Set Rng = Nothing

End Sub

 

[Bernie Deitrick]

Loraine,

IIRC, you cannot have more than 8,000 or so blank cells.

To fix it, you could step down the rows, like this:.

Sub FillBlanks3()
Dim Rng As Range
Dim myRow As Range

On Error Resume Next
Set Rng = ActiveSheet.UsedRange.Columns("A")
For Each myRow In Rng.Rows
With myRow
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Copy
.PasteSpecial xlPasteValues
End With
Next myRow
Set Rng = Nothing
End Sub


Or, if that is too slow, in bigger chunks:

Sub FillBlanks4()
Dim Rng As Range
Dim i As Long

On Error Resume Next
Set Rng = ActiveSheet.UsedRange.Columns("A")
For i = 1 To Rows.Count Step 2000
With Intersect(Rng, Range(i & ":" & i + 2000))
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Copy
.PasteSpecial xlPasteValues
End With
Next i
Set Rng = Nothing
End Sub


HTH,
Bernie
MS Excel MVP

 

[Guest]

Thank you. I use fillBlanks4 and it worked beautifully.....

Loraine,

IIRC, you cannot have more than 8,000 or so blank cells.

To fix it, you could step down the rows, like this:.

Sub FillBlanks3()
Dim Rng As Range
Dim myRow As Range

On Error Resume Next
Set Rng = ActiveSheet.UsedRange.Columns("A")
For Each myRow In Rng.Rows
With myRow
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Copy
.PasteSpecial xlPasteValues
End With
Next myRow
Set Rng = Nothing
End Sub


Or, if that is too slow, in bigger chunks:

Sub FillBlanks4()
Dim Rng As Range
Dim i As Long

On Error Resume Next
Set Rng = ActiveSheet.UsedRange.Columns("A")
For i = 1 To Rows.Count Step 2000
With Intersect(Rng, Range(i & ":" & i + 2000))
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Copy
.PasteSpecial xlPasteValues
End With
Next i
Set Rng = Nothing
End Sub


HTH,
Bernie
MS Excel MVP

I have a spreadsheet that has 44,383 rows. I am trying to use the following
macro to fill in the blanks for columns A. I am getting a REF error. I
know it is the number of rows because the macro works for spreadsheets that
do not have as many rows. Is there any modification to this code that will
make the macro fill down for 44,383 rows:

Sub FillBlanks2()
Dim Rng As Range
On Error Resume Next
Set Rng = ActiveSheet.UsedRange.Columns("A")
With Rng
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Copy
.PasteSpecial xlPasteValues
End With
Set Rng = Nothing

End Sub