VBA Assign values between numbers to array

Question

I have an excel file with 430 columns and I need to loop through columns 28-32 39-56 60-71 89-268 276-291 323-396 416-418 420-429

For that purpose, I am creating an array, but I don't how I can assign all values between each bounds. The array should contain the values 28, 29, 30, 31, 32, 39, 40,... and so on.

I've been looking, but I might not be using the correct keywords.

What do you mean by assigning the array values between each bounds? You received a (clever) answer showing how to treat the bounds part. It would be good if you better explain what the mentioned assigning process does mean. The bounds do not have the same number of columns. Should the code assign the array on each such column belonging to the mentioned bounds? If not, please better explain what you want accomplishing. The array you show us does not contain something to be associated to above bounds definition... — FaneDuru
– FaneDuru, Commented Dec 28, 2021 at 17:13
If you assign these column numbers to an array, as you indicated, you shouldn't need to know the bounds of the different column ranges. Each column number will be stored in an array element and the element indexes will be sequential from 0 to n (assuming default Option Base 0). So you would loop through each array element, each of which is one of your column numbers. — bugdrown
– bugdrown, Commented Dec 28, 2021 at 17:26
@FaneDuru i meant the bounds between each interval, the left- and right bounds. The array I show contains the values in the first interval, the 2nd interval, and so on. It seems "bounds" means something else in VBA ? — DanielMc
– DanielMc, Commented Dec 28, 2021 at 17:40
@bugdrown one of the goals was not to write each element of these intervals, too time consuming — DanielMc
– DanielMc, Commented Dec 28, 2021 at 17:45

BigBen · Accepted Answer · 2021-12-28 17:03:46Z

4

Rather than using an array, one alternative might be to use Select Case, something like this:

Sub tester()
    Dim col As Range
    For Each col In ActiveSheet.Range("AB:PM").Columns
        Select Case col.Column
            Case 28 To 32, 39 To 56, 60 To 71 ' and so on
                ' Process col here       
        End Select
    Next
End Sub

answered Dec 28, 2021 at 17:03

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Assign values between numbers to array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related