Columns collection Address
 

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

Columns collection Address
 

On Feb 14, 12:43 pm, Vlado Sveda
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

Columns collection Address
 

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" wrote in message
...
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

Columns collection Address
 

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




" wrote:

On Feb 14, 12:43 pm, Vlado Sveda
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

Columns collection Address
 

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.C ells)
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

Columns collection Address
 

I solved my problem as you can see up.

Thank to all !
Vlado