Filling blank Cell With 0 in Dynamical range .

[Panki]

Below Code is giving me the message of Blank cells in dynamical range. I want to replace all blank cells with "0".

Note - Range should be dynamic on the basis of A2 to last column as per Data and A2 to last Row as per Data .it is not fixed.



Sub Blank_Cell_Alert()
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "MyRange"
ActiveSheet.Name = "MySheet"
If Range("MyRange").SpecialCells(xlCellTypeBlanks).Count > 0 Then
MsgBox ("Please Enter all Employee Data Before you Attempt to Update this Table."), vbCritical, "Incomplete Data!"
Cancel = True
End If
End Sub

 

[portews]

Try this:

Sub Blank_Cell_Replacement()
Dim MyRange As Range
Dim cel As Variant
Dim ColCount As Long
Dim RowCount As Long
'You don't need to select the cells to make the range, just count them
ColCount = Range("A2", Range("A2").End(xlToRight)).Columns.Count
RowCount = Range("A2", Range("A2").End(xlDown)).Rows.Count + 1 'add one for starting at row 2
'set the range based on your counts
Set MyRange = Range("A2", Cells(RowCount, ColCount))
If MyRange.SpecialCells(xlCellTypeBlanks).Count > 0 Then
    'loop through all the cells in the range
    For Each cel In MyRange
        'if the cell is empty , make it 0
        If cel = "" Then cel.Value = "0"
    Next
End If
End Sub