Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Columns collection Address
I solved my problem as you can see up.
Thank to all ! Vlado |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Address blocks to columns | Excel Discussion (Misc queries) | |||
Two columns only take one name for both email address | Excel Worksheet Functions | |||
Separate address column to Five columns | Excel Worksheet Functions | |||
excel columns collection? | Excel Programming | |||
Address labels to columns | Excel Discussion (Misc queries) |