We help IT Professionals succeed at work.

Macro to fill blank cells

RishiSingh05
RishiSingh05 asked
on
In the attached Excel file, Sheet1 has values in Col A and Col B with blank rows in between.  I am looking for a macro which will read the values in A2 and B2 and copy in the blank cells below it until it finds the next set of values, then it will copy those down.  The last set of values it finds, it must copy down in 9 rows below it.  See Sheet2 to see how the blanks should be filled.  Thanks.
EEfillblanks.xls
Comment
Watch Question

Top Expert 2008

Commented:
Call the first macro, which will work with the second to achieve your goal

Sub CopyDownPlus9
Dim lRowLoop As Long, lLastRow As Long

lLastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
range("A2:B" & llastrow+9).select
call copydownempty

end sub

Sub CopyDownEmpty()
'This macro takes the selected range and for each empty cell, _
copies the value of the cell above
Dim cl As range, rng As range

Set rng = selection

application.ScreenUpdating = False 'turns off screen updating

If application.WorksheetFunction.CountIf(rng, "") > 0 Then
    rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C"
    rng.EntireColumn.Copy
    rng.EntireColumn.PasteSpecial xlPasteValues
Else
    MsgBox "No empty cells to fill"
End If

application.CutCopyMode = False

application.ScreenUpdating = True 'turns screen updating back on
End Sub

Open in new window


Thomas

Author

Commented:
Thanks, Thomas.  Will give it a try, and report back ,,,
You can do this without a macro. Select columns 1 and 2, F5 > Special > Blanks, then enter =A2 in the formula bar and press Ctrl+Enter.

Author

Commented:
Thomas, it doesn't quite do it.  In the attached file, Sheet1 has my initial data.  Sheet2 has the data after I run the macro. Pls take a look at how it handles the first values and the last values.  Thanks.
EE2.xls

Author

Commented:
Stephen, I select cols 1 and 2 and hit F5 and nothing at all happens.  No window or drop-down opens for me to select Special .... Blanks
Top Expert 2008

Commented:
Weird indeed, I have messed up one of my macros. Here it is:

Sub CopyDownPlus9()

Dim lRowLoop As Long, lLastRow As Long

lLastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:B" & lLastRow + 9).Select
Call CopyDownEmpty

End Sub

Sub CopyDownEmpty()
'This macro takes the selected range and for each empty cell, _
copies the value of the cell above
Dim cl As Range, rng As Range

Set rng = Selection

Application.ScreenUpdating = False 'turns off screen updating

If Application.WorksheetFunction.CountIf(rng, "") > 0 Then
    rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    rng.EntireColumn.Copy
    rng.EntireColumn.PasteSpecial xlPasteValues
Else
    MsgBox "No empty cells to fill"
End If

Application.CutCopyMode = False

Application.ScreenUpdating = True 'turns screen updating back on


End Sub

Open in new window

Author

Commented:
Thomas, a lot better.  But it does not do anything to the last values.  It should copy it and paste in the 9 rows below.
That's strange. Well Thomas's macro is more or less equivalent, but if F5 doesn't work you could try Home > Find and Select > Goto Special and then select Blanks. Continue as before.
Top Expert 2008
Commented:
Sorry it's taking me so long;

Sub CopyDownPlus9()

Dim lRowLoop As Long, lLastRow As Long

lLastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

With Range("A2:B" & lLastRow + 9)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    Range("A" & lLastRow + 1 & ":B" & lLastRow + 9).FormulaR1C1 = "=R[-1]C"
    .EntireColumn.Copy
    .EntireColumn.PasteSpecial xlPasteValues
End With

Application.CutCopyMode = False

End Sub

Open in new window

Top Expert 2008

Commented:
the specialcells blanks only work within the current used range. As the 9 rows below are outside of the used range, they're not even blanks, they don't exist. That's why the Select Special or my macro weren't working for that.

T

Author

Commented:
Thomas, it works well now.  

Stephen, I like your solution too even though it does not do anything to the last value (which is understandable).  In a pinch it would be good for me to know how to do it this way.

My thanks, gentlemen.

Author

Commented:
Stephen, actually your solution works to perfection.

Author

Commented:
Also, to make my Function keys work I had to hit the "F Lock" button on my keyboard.  For some reason, it was turned off.