Set all empty cells to zero except for certain columns-CodePudding

I have a file called "gar_nv", "nbrLines" is the number of lines ,defined in my code. I have given names to my columns. "listCol" is a function returning a list of these names. I would like to set all empty cells to zero except the cells of the following columns: "GCFRRE", "GCDEP1", "GCDEP2", "GCDEP3", "GCTYC0", "GCTYC1", "GCTYC2", "GCTYC3","GCBAC0", "GCBAC1", "GCBAC2", "GCBAC3". Knowing I have thousands of rows, this code takes a long time to run. Is there a way to make it faster ?

Dim rng As Variant, i As Long

With gar_nv
    For i = 1 To nbrLines - 1
        For Each rng In ListCol
            Select Case rng
            Case "GCFRRE", "GCDEP1", "GCDEP2", "GCDEP3", _
            "GCTYC0", "GCTYC1", "GCTYC2", "GCTYC3", _
            "GCBAC0", "GCBAC1", "GCBAC2", "GCBAC3"
            
            Case Else
                If IsEmpty(.range(rng).Rows(i)) = True Then
                .range(rng).Rows(i).Value = "0"
                End If
            End Select
        Next rng
    Next i
End With

CodePudding user response：

Let's say your columns are named ranges like headers in the image:

You can do:

Sub test()
Application.ScreenUpdating = False
Dim rng As Range
Dim Listcol As Variant
Dim i As Long
Listcol = Array("A", "B", "C_", "D") 'list of all named ranges

For i = 0 To UBound(Listcol)
    Select Case Listcol(i)
        Case "B"
            'we do nothing
        Case Else
            'we replace blanks with 0
            Set rng = Range(Listcol(i)).SpecialCells(xlCellTypeBlanks)
            rng.FormulaR1C1 = "=0" ' set them to 0
            rng.Value = rng.Value 'replace formula with value
            Set rng = Nothing
    End Select
Next i

Erase Listcol

Application.ScreenUpdating = True

End Sub

Notice named range B has been excluded: