Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill Blanks Macro
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill Blanks Macro
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill Blanks Macro
Thank you. I use fillBlanks4 and it worked beautifully.....
"Bernie Deitrick" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill in the blanks to smooth my projection curve | Excel Discussion (Misc queries) | |||
Fill column blanks from last non-blank cell | Excel Discussion (Misc queries) | |||
How do I fill in pivot table blanks? | Excel Worksheet Functions | |||
How do I auto fill long col. filling blanks with last filled cell | Excel Discussion (Misc queries) | |||
How do I auto fill blanks cells immediately below with the same i. | Excel Discussion (Misc queries) |