Store Multiple Non Sequential Column References in Array and then Select these columns

**Jimbo77** · 04-24-2014, 04:55 PM

Hello all

I need some code that does the follow:

1) Starting in Column A looks to see if any if any cells in column contain the number 1.

2) If any do then store this column reference in an array.

3) Move on to next column.

4) Repeat 2) and 3) until no data in column

5) Select the columns from the array

Is this possible?

Jim

**Kaper** · 04-25-2014, 01:47 AM

You can add column one by one to a range by means of UNION

Something like

dim myrange as range, i as long
for i = 1 to 100 
  if your condition for column i is true then
    if myrange is nothing then 
      set myrange = columns(i)
   else
     set myrange = union(myrange,columns(i)) 
   end if
 end if 
next i
if myrange is nothing then
  msgbox "sorry nothing found"
else
  ' do the job :)
end if

PS. For checking if column contains particular value you could use either Find or worksheetfunction.countif

**Jimbo77** · 04-25-2014, 02:12 PM

Awesome - this was the key bit for me to use:

if myrange is nothing then 
      set myrange = columns(i)
   else
     set myrange = union(myrange,columns(i)) 
   end if

Store Multiple Non Sequential Column References in Array and then Select these columns

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Store Multiple Non Sequential Column References in Array and then Select these columns

Re: Store Multiple Non Sequential Column References in Array and then Select these columns

Re: Store Multiple Non Sequential Column References in Array and then Select these columns

Thread Information

Users Browsing this Thread

Similar Threads

Adding IF(ISERROR) to multiple and non-sequential cell references

Copy non-sequential columns with headers (from multiple sheets) to one consolidated sheet

Select range dynamically using relative row and column references

Filter 2 or more columns, store result in one column

array/lookup help - multiple references over multiple colums

Bookmarks

Bookmarks

Posting Permissions