I'm searching the simplest way how to address uncontinuos (sub)range of
columns.
Imagine that you want to do something with columns 4 (= "D"), 7 (= "G"), 8
(= "H") and 12 (="L"). I'm trying to find something like:
For Each MyCell In MyRange.Columns(4, 7, 8, 12).Cells
..... do something
Next MyCell

So my question is, how to address Columns(4, 7, 8, 12) by the simplest way.

I welcome every good idea.

Vlado

On Feb 14, 12:43 pm, Vlado Sveda
<VladoSv...@discussions.microsoft.com> wrote:
Hi
You could do
Dim ColumnsToCount(1 to 4) as long
ColumnsTocount(1) = 4
ColumnsTocount(2) = 7
ColumnsTocount(3) = 8
ColumnsTocount(4) = 12
RowsToCount = MyRange.Rows.Count
For i = 1 to RowsToCount

> I'm searching the simplest way how to address uncontinuos (sub)range of
> columns.
> Imagine that you want to do something with columns 4 (= "D"), 7 (= "G"), 8
> (= "H") and 12 (="L"). I'm trying to find something like:
> For Each MyCell In MyRange.Columns(4, 7, 8, 12).Cells
> ..... do something
> Next MyCell
>
> So my question is, how to address Columns(4, 7, 8, 12) by the simplest way.
>
> I welcome every good idea.
>
> Vlado

Dim cell As Range
For Each cell In Range("D1,G1,H1,L1").EntireColumn.Cells
MsgBox cell.Address
Next cell


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Vlado Sveda" <(E-Mail Removed)> wrote in message
news:B2AA3CD6-278A-4C64-9B70-(E-Mail Removed)...
> I'm searching the simplest way how to address uncontinuos (sub)range of
> columns.
> Imagine that you want to do something with columns 4 (= "D"), 7 (= "G"), 8
> (= "H") and 12 (="L"). I'm trying to find something like:
> For Each MyCell In MyRange.Columns(4, 7, 8, 12).Cells
> ..... do something
> Next MyCell
>
> So my question is, how to address Columns(4, 7, 8, 12) by the simplest
way.
>
> I welcome every good idea.
>
> Vlado

Thanks Paul,
but it isn't what I was looking for.

Meanwhile I came up with this solution:

Set MyRange = ActiveSheet.Cells(FIRST_ROW, FIRST_COLUMN).CurrentRegion

Set MyRange2 = Application.Union(MyRange.Columns(4), _
MyRange.Columns(7), _
MyRange.Columns(8), _
MyRange.Columns(12))
For Each MyCell In MyRange2.Cells
..... do something
Next MyCell

Nevertheless thank you for your help

Vlado




"(E-Mail Removed)" wrote:

> On Feb 14, 12:43 pm, Vlado Sveda
> <VladoSv...@discussions.microsoft.com> wrote:
> Hi
> You could do
> Dim ColumnsToCount(1 to 4) as long
> ColumnsTocount(1) = 4
> ColumnsTocount(2) = 7
> ColumnsTocount(3) = 8
> ColumnsTocount(4) = 12
> RowsToCount = MyRange.Rows.Count
> For i = 1 to RowsToCount
>
> > I'm searching the simplest way how to address uncontinuos (sub)range of
> > columns.
> > Imagine that you want to do something with columns 4 (= "D"), 7 (= "G"), 8
> > (= "H") and 12 (="L"). I'm trying to find something like:
> > For Each MyCell In MyRange.Columns(4, 7, 8, 12).Cells
> > ..... do something
> > Next MyCell
> >
> > So my question is, how to address Columns(4, 7, 8, 12) by the simplest way.
> >
> > I welcome every good idea.
> >
> > Vlado
>
>
>

And building on Bob's response, maybe...

Dim cell As Range
dim myCols as range
dim myRange as range

Set myrange = somethinggoeshere

set mycols = nothing
on error resume next
set mycols = intersect(myrange, _
myrange.parent.Range("D1,G1,H1,L1").EntireColumn.Cells)
on error goto 0

if mycols is nothing then
msgbox "no cols!
'exit sub '???
else
for eacy cell in mycols.cells
MsgBox cell.Address
Next cell
end if

Vlado Sveda wrote:
>
> I'm searching the simplest way how to address uncontinuos (sub)range of
> columns.
> Imagine that you want to do something with columns 4 (= "D"), 7 (= "G"), 8
> (= "H") and 12 (="L"). I'm trying to find something like:
> For Each MyCell In MyRange.Columns(4, 7, 8, 12).Cells
> ..... do something
> Next MyCell
>
> So my question is, how to address Columns(4, 7, 8, 12) by the simplest way.
>
> I welcome every good idea.
>
> Vlado

--

Dave Peterson

I solved my problem as you can see up.

Thank to all !
Vlado