Using Range - any idea why this line doesn't work?

Hi I am attempting to write a PO submission and approval system. I'm using one worksheet as an entry template and another as a approval template

The line below sets the range for cells that could be completed. It worked with just a few references, but now fails. Is there a maximum number of references in a range command like this?


Set InputRng = ThisWorkbook.Worksheets("New_po").Range("D19, H49, H18, D18, D20, D22, C5, C7, J40, J36, J37, J38, J39, J43, J41, J42, C8, C9, C10, C11, C13, C14, C15, H5, H7, H8, H9, H10, H11, H13, H14, H15, B26, C26, H26, I26, J26, B27, C27, H27, I27, J27, B28, C28, H28, I28, J28, B29, C29, H29, I29, J29, B30, C30, H30, I30, J30, B31, C31, H31, I31, J31, B32, C32, H32, I32, J32, B33, C33, H33, I33, J33, B34, C34, H34, I34, J34, B35, C35, H35, I35, J35")

Sandant

This may not be the answer you are looking for - but a suggestion. I would never create a range doing your methods as it is too difficult to debug and maintain if the spreadsheet changes. I do not have any suggestions for improving that as I dont see a sequence to your range selection. What I do in cases when I need a range of a number of different cells is to use the UNION command. I often loop through the range of cells and add one at a time.

While I do not recommend that you code it this way, this will solve your problem.

Sub test()

Dim inputrng As Excel.Range
'Set inputrng = ThisWorkbook.Worksheets("New_po").Range("D19, H49, H18, D18, D20, D22, C5, C7, J40, J36, J37, J38, J39, J43, J41, J42, C8, C9, C10, C11, C13, C14, C15, H5, H7, H8, H9, H10, H11, H13, H14, H15, B26, C26, H26, I26, J26, B27, C27, H27, I27, J27, B28, C28, H28, I28, J28, B29, C29, H29, I29, J29, B30, C30, H30, I30, J30, B31, C31, H31, I31, J31, B32, C32, H32, I32, J32, B33, C33, H33, I33, J33, B34, C34, H34, I34, J34, B35, C35, H35, I35, J35")


Set inputrng = ThisWorkbook.Worksheets("New_po").Range("D19, H49, H18, D18, D20, D22, C5, C7, J40, J36, J37, J38, J39, J43, J41, J42, C8, C9, C10, C11, C13, C14, C15, H5, H7, H8, H9, H10, H11, H13, H14, H15, B26, C26, H26, I26, J26, B27, C27, H27, I27, J27, B28, C28, H28, I28, J28, B29, C29, H29, I29, J29, B30")
Set inputrng = Application.Union(inputrng, Range(" C30, H30, I30, J30, B31, C31, H31, I31, J31, B32, C32, H32, I32, J32, B33, C33, H33, I33, J33, B34, C34, H34, I34, J34, B35, C35, H35, I35, J35"))
inputrng.Select
End Sub


Hope this helps

Hello sandant,

There is a maximum but I don't recall the limit. Here is another looping method using Union.


Dim RngArray As Variant
Dim InputRng As Range
Dim Wks As Worksheet

Set Wks = ThisWorkbook.Worksheets("New_po")

RngArray = Split("D19,H49,H18,D18,D20,D22,C5,C7,J40,J36,J37,J38,J39,J43,J41,J42,C8,C9,C10,C11,C13,C14,C15,H5,H7,H8,H9,H10,H11,H13,H14,H15,B26,C26,H26,I26,J26,B27,C27,H27,I27,J27,B28,C28,H28,I28,J28,B29,C29,H29,I29,J29,B30,C30,H30,I30,J30,B31,C31,H31,I31,J31,B32,C32,H32,I32,J32,B33,C33,H33,I33,J33,B34,C34,H34,I34,J34,B35,C35,H35,I35,J35", ",")

For Each RngItem In RngArray
If InputRng Is Nothing Then Set InputRng = Wks.Range(RngItem)
Set InputRng = Union(Wks.Range(RngItem), InputRng)
Next RngItem