Fill Blanks Macro

L

LLoraine

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:D. 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:D")
With Rng
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Copy
.PasteSpecial xlPasteValues
End With
Set Rng = Nothing

End Sub
 
B

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:D")
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:D")
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
 
L

LLoraine

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

Bernie Deitrick said:
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:D")
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:D")
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


LLoraine said:
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:D. 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:D")
With Rng
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Copy
.PasteSpecial xlPasteValues
End With
Set Rng = Nothing

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top