CODEWITHSUNDEEP
excelvba
Sunaina
Sunaina

Reputation: 67

Filling of blank cells with zero using VBA code

Sub FillEmptyCell()
    Dim rng As Range
    Dim i As Long
    Dim cell As Range
    Dim sht As Worksheet
    Set sht = ActiveWorkbook.Sheets("Sheet1")
    sht.Activate

    Set rng = Range(Range("C12"), Range("AD" & sht.UsedRange.Rows.Count))

    For Each cell In rng
        If cell.Value = "" Then
            cell.Value = "0"
        End If

    Next
End Sub

I am trying to fill my blank spaces in sheet with zero dynamically.However, I don't want this to fill zeroes in row that have no data. can someone help please?

Upvotes: 1

Views: 11780

Answers (1)

Davesexcel
Davesexcel

Reputation: 6984

See how this works,

Sub ZeroStuff()
    Dim LstRw As Long, rng As Range, sh As Worksheet, c As Range

    Set sh = Sheets("Sheet1")

    With sh
        LstRw = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
        Set rng = .Range("C12:C" & LstRw).SpecialCells(xlCellTypeBlanks)

        For Each c In rng.Cells
            .Range(c.Offset(, 1), c.Offset(, 27)) = 0
        Next c

    End With

End Sub

Upvotes: 1

Related Questions