[Solved] Fill Blanks Error

[Some one Far Away]

Hello Everyone,

I am new to VBA and would like to seek your help in the follwoing code:

ange("A1").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Application.CutCopyMode = False
Selection.FormulaR1C1 = "=R[-1]C"
Selection.CurrentRegion.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

when I reach the bolded like a get a message of an error, any idea what it might be?

Regards,

 

[Adrey]

Dear Some one Far Away,
Hope you are doing well.
The VBA code you provided seems to have a potential error related to the usage of Selection.SpecialCells(xlCellTypeBlanks).Select.

When using the SpecialCells method with xlCellTypeBlanks, there is a possibility of encountering a run-time error if there are no blank cells within the selected range. The error occurs because the SpecialCells method will throw an exception if it doesn't find any cells with the specified criteria.

Try this Code Instead zof that:

Sub BlankCells()
     On Error Resume Next
    Range("A1").Select
    Selection.CurrentRegion.Select
 
    On Error GoTo ErrorHandler
    Selection.SpecialCells(xlCellTypeBlanks).Select
    On Error GoTo 0
 
    Application.CutCopyMode = False
    Selection.FormulaR1C1 = "=R[-1]C"
    Selection.CurrentRegion.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
 
    Exit Sub
 
ErrorHandler:
    MsgBox "No blank cells found.", vbExclamation
End Sub

Hope, this may solve your problem. If you are still facing the problem, we recommend you to share your file or at least a screenshot of the entire problem so that we can get back to you with the exact solution.
Regards,
Raiyan Zaman Adrey || ExcelDemy