VB basics

[dopydette]

how can I simplify this code? thank you for your help

Range("SO_Bid_1_1") = 0
Range("SO_Bid_1_2") = 0
Range("SO_Bid_1_3") = 0
Range("SO_Bid_1_4") = 0
Range("SO_Bid_1_5") = 0
Range("SO_Bid_1_6") = 0
Range("SO_Bid_1_7") = 0
Range("SO_Bid_1_8") = 0
Range("SO_Bid_1_9") = 0
Range("SO_Bid_1_10") = 0
Range("SO_Bid_1_11") = 0
Range("SO_Bid_1_12") = 0
Range("SO_Bid_1_13") = 0
Range("SO_Bid_1_14") = 0
Range("SO_Bid_1_15") = 0
Range("SO_Bid_1_16") = 0
Range("SO_Bid_1_1n") = 0
Range("SO_Bid_1_2n") = 0
Range("SO_Bid_1_3n") = 0
Range("SO_Bid_1_4n") = 0
Range("SO_Bid_1_5n") = 0
Range("SO_Bid_1_6n") = 0
Range("SO_Bid_1_7n") = 0
Range("SO_Bid_1_8n") = 0
Range("SO_Bid_1_9n") = 0
Range("SO_Bid_1_10n") = 0
Range("SO_Bid_1_11n") = 0
Range("SO_Bid_1_12n") = 0
Range("SO_Bid_1_13n") = 0
Range("SO_Bid_1_14n") = 0
Range("SO_Bid_1_15n") = 0
Range("SO_Bid_1_16n") = 0

Range("SO_Bid_2_1") = 0
Range("SO_Bid_2_2") = 0
Range("SO_Bid_2_3") = 0
Range("SO_Bid_2_4") = 0
Range("SO_Bid_2_5") = 0
Range("SO_Bid_2_6") = 0
Range("SO_Bid_2_7") = 0
Range("SO_Bid_2_8") = 0
Range("SO_Bid_2_9") = 0
Range("SO_Bid_2_10") = 0
Range("SO_Bid_2_11") = 0
Range("SO_Bid_2_12") = 0
Range("SO_Bid_2_13") = 0
Range("SO_Bid_2_14") = 0
Range("SO_Bid_2_15") = 0
Range("SO_Bid_2_16") = 0
Range("SO_Bid_2_1n") = 0
Range("SO_Bid_2_2n") = 0
Range("SO_Bid_2_3n") = 0
Range("SO_Bid_2_4n") = 0
Range("SO_Bid_2_5n") = 0
Range("SO_Bid_2_6n") = 0
Range("SO_Bid_2_7n") = 0
Range("SO_Bid_2_8n") = 0
Range("SO_Bid_2_9n") = 0
Range("SO_Bid_2_10n") = 0
Range("SO_Bid_2_11n") = 0
Range("SO_Bid_2_12n") = 0
Range("SO_Bid_2_13n") = 0

 

[schielrn]

Are those the only named ranges that start with SO_Bid?

 

[dopydette]

yes - it goes up to SO_Bid_5 in the same pattern and the same is done to every name
thanks

 

[texasalynn]

would this work

Sub SO_Bid()
Dim i As Long, j As Long
For i = 1 To 16
    Range("SO_Bid_1_" & i) = 0
    Range("SO_Bid_1_" & i & "n") = 0
Next i

For j = 1 To 16
    Range("SO_Bid_2_" & i) = 0
    Range("SO_Bid_2_" & i & "n") = 0
Next j

End Sub

 

[schielrn]

Try this:

Sub setToZero()
For Each n In ActiveWorkbook.Names
    If Left(n.Name, 6) = "SO_Bid" Then Range(n.Name).Value = 0
Next n
End Sub

But this looks at all the names in a workbook and not a specific sheet.

Hope that helps.

 

[doofusboy]

If those are all range names, you could use:

Dim n As Name
    For Each n In ThisWorkbook.Names
        If Mid(n.Name, 1, 6) = "SO_Bid" Then
            Range(n.Name).Value = 0
        End If
    Next n

 

[dopydette]

that's perfect thank you - the names are across several sheets