VBA Set named columns to zero-CodePudding

In my worksheet "gar_nv", I have a list of columns that I named "GCPCC0", "GCPCC1" and "GCPCC2. I would like to set them to zero starting the 2nd cell.

Sub CstVal()

Call Workbook_open

Dim Name As Variant
Dim i As Long

With gar_nv
    nbr_lines = .UsedRange.Rows.Count
    List = Array("GCCP0", "GCPCC1", "GCPCC2")
    For Each Name In List
        For i = 1 To nbr_lines
            .Range(Name).Rows(i).Value = "0"
            
        Next i
    Next
    
    
End With
End Sub

This line throws a 1004 error, how can I fix it ?

.Range(Name).Rows(i).Value = "0"

CodePudding user response：

Fill Column Data Ranges (the Range Below the Headers)

Option Explicit

Sub CstVal()

    'Call Workbook_open ' irrelevant
    
    With gar_nv
        
        Dim nmList As Variant: nmList = Array("GCCP0", "GCPCC1", "GCPCC2")
        
        Dim nm As Variant
        
        For Each nm In nmList
            With .Range(nm)
                'Debug.Print .Address, .Resize(.Rows.Count - 1).Offset(1).Address
                .Resize(.Rows.Count - 1).Offset(1).Value = 0
            End With
        Next nm
    
    End With

End Sub

CodePudding user response：

You can try:

1st option:

.Range(Name & i).Value = "0"

2nd option:

.Cells(i,Name).Value = "0"

Hope it works.