Limit to Non Contiguous range selection workaround

[smpatty08]

Below I have posted my code that updates a fill down list, but The highlighted section in red gives me an error if I make the range selection bigger. The range has non contiguous cells that need the fill down list updated all the way down to cell 467. Is there a work around for this? Maybe using an advanced For Loop, but the cells aren't a constant distance apart. There are 7 cells that are skip a row from C7 to C19, but then it would need to skip 20 rows to C39 then select every other cell for 14 rows then skip 20 again and so on. I do not know what would be faster. Maybe using Union()? but I have never used that function before. Any help would be appreciated! Thanks!

Sub FillDownList()Dim WB1 As Workbook, WS1 As Worksheet, WS2 As Worksheet, rng1 As String, rng2 As String, rng3 As String, cell As Range
Application.ScreenUpdating = False
Set WB1 = ActiveWorkbook
Set WS1 = WB1.Sheets("Reagent Preparation")
Set WS2 = WB1.Sheets("Reagent References")
Set cell = ActiveCell
On Error Resume Next
WS1.Unprotect
WS2.Activate
WS2.Range("B123:D1000").ClearContents
WS2.Columns(2).AutoFilter Field:=1, Criteria1:="<>"
WS2.Range("B1", Range("B1").End(xlDown)).SpecialCells(xlCellTypeVisible).Copy
WS2.Range("B123").PasteSpecial xlPasteValues
WS2.AutoFilterMode = "False"
ActiveSheet.Range("B123", Range("B123").End(xlDown)).RemoveDuplicates Columns:=(1), Header:=xlYes
WS2.Columns(3).AutoFilter Field:=1, Criteria1:="<>"
WS2.Range("C1", Range("C1").End(xlDown)).SpecialCells(xlCellTypeVisible).Copy
WS2.Range("C123").PasteSpecial xlPasteValues
WS2.AutoFilterMode = "False"
ActiveSheet.Range("C123", Range("C123").End(xlDown)).RemoveDuplicates Columns:=(1), Header:=xlYes
WS2.Columns(4).AutoFilter Field:=1, Criteria1:="<>"
WS2.Range("D1", Range("D1").End(xlDown)).SpecialCells(xlCellTypeVisible).Copy
WS2.Range("D123").PasteSpecial xlPasteValues
WS2.AutoFilterMode = "False"
ActiveSheet.Range("D123", Range("D123").End(xlDown)).RemoveDuplicates Columns:=(1), Header:=xlYes


rng1 = "'Reagent References'!" & WS2.Range("B124", Range("B124").End(xlDown)).Address
rng2 = "'Reagent References'!" & WS2.Range("C124", Range("C124").End(xlDown)).Address
rng3 = "'Reagent References'!" & WS2.Range("D124", Range("D124").End(xlDown)).Address
WS1.Activate
[COLOR=#ff0000]WS1.Range("$C$7:$E$7,$C$9:$E$9,$C$11:$E$11,$C$13:$E$13,$C$15:$E$15,$C$17:$E$17,$C$19:$E$19,$C$39:$E$39,$C$41:$E$41,$C$43:$E$43,$C$45:$E$45,$C$47:$E$47,$C$49:$E$49,$C$51:$E$51,$C$71:$E$71,$C$73:$E$73,$C$75:$E$75,$C$77:$E$77,$C$79:$E$79,$C$81:$E$81,$C$83:$E$83").Select[/COLOR]
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=" & rng1
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = False
End With
WS1.Range("$G$7:$J$7,$G$9:$J$9,$G$11:$J$11,$G$13:$J$13,$G$15:$J$15,$G$17:$J$17,$G$19:$J$19,$G$39:$J$39,$G$41:$J$41,$G$43:$J$43,$G$45:$J$45,$G$47:$J$47,$G$49:$J$49,$G$51:$J$51,$G$71:$J$71,$G$73:$J$73,$G$75:$J$75,$G$77:$J$77,$G$79:$J$79,$G$81:$J$81,$G$83:$J$83").Select
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=" & rng2
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = False
End With
WS1.Range("$L$7:$M$7,$L$9:$M$9,$L$11:$M$11,$L$13:$M$13,$L$15:$M$15,$L$17:$M$17,$L$19:$M$19,$L$39:$M$39,$L$41:$M$41,$L$43:$M$43,$L$45:$M$45,$L$47:$M$47,$L$49:$M$49,$L$51:$M$51,$L$71:$M$71,$L$73:$M$73,$L$75:$M$75,$L$77:$M$77,$L$79:$M$79,$L$81:$M$81,$L$83:$M$83").Select
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=" & rng3
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = False
End With
cell.Select
WS1.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

The maximum length for the text argument for the Range property is 255 (or maybe 256) characters total. The only thing you can do is break that argument up into smaller pieces and run each of them separately. Your best bet would be to put the smaller text pieces into an array and then use a For..Next loop to iterate each one of them.

 

[smpatty08]

The maximum length for the text argument for the Range property is 255 (or maybe 256) characters total. The only thing you can do is break that argument up into smaller pieces and run each of them separately. Your best bet would be to put the smaller text pieces into an array and then use a For..Next loop to iterate each one of them.

This code runs when cells are changed so I want it to be fast. Would it be faster to do what you suggest or just run my same code 5 times to cover the entire range I need?

[COLOR=#FF0000]WS1.Range("$C$7:$E$7,$C$9:$E$9,$C$11:$E$11,$C$13:$E$13,$C$15:$E$15,$C$17:$E$17,$C$19:$E$19,$C$39:$E$39,$C$41:$E$41,$C$43:$E$43,$C$45:$E$45,$C$47:$E$47,$C$49:$E$49,$C$51:$E$51,$C$71:$E$71,$C$73:$E$73,$C$75:$E$75,$C$77:$E$77,$C$79:$E$79,$C$81:$E$81,$C$83:$E$83").Select[/COLOR]With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=" & rng1
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = False [COLOR=#333333]End With[/COLOR]

 

[shg]

You could select all the cells of interest on the worksheet and give them a name, then use

with ws1.range("Bob")