Trouble with columns and for each
 

Hi Excel experts

I working on this code and i don't know why no run well.

I have a lot data in the Current region

Option Explicit

Sub Test()
Dim myrange As Range
Dim mycell As Range
Set myrange = Range("B2").CurrentRegion.Columns(1)
For Each mycell In myrange
mycell.Select
Next mycell
End Sub

Thanks for your expert advice


Regards


Fernando

Trouble with columns and for each
 

Hi Fernando,

The problem is caused by the fact that you are selecting just the column 1
within the currentregion. Thus, myCell is being treated as a column range,
and so there is only one instance of myCell in myRange, they are the same
range, so the loop only iterates once.

Try this alternate version

Dim myrange As Range
Dim mycell As Range
Set myrange = Range("B2", Range("B2").End(xlDown))
myrange.Select
For Each mycell In myrange
mycell.Select
Next mycell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Fernando Ortiz" wrote in message
...
Hi Excel experts

I working on this code and i don't know why no run well.

I have a lot data in the Current region

Option Explicit

Sub Test()
Dim myrange As Range
Dim mycell As Range
Set myrange = Range("B2").CurrentRegion.Columns(1)
For Each mycell In myrange
mycell.Select
Next mycell
End Sub

Thanks for your expert advice


Regards


Fernando

Trouble with columns and for each
 

Fernando,

I assume that you want your cells ( in the first column of your current
region) selected one by one.

If so, try :

Option Explicit
Sub Test()
Dim NrRows As Integer, I As Integer
Dim myrange As Range
Set myrange = Range("B2").CurrentRegion.Columns(1)
NrRows = myrange.Rows.Count
For I = 1 To NrRows
Cells(I, 1).Select
Next
End Sub

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"Fernando Ortiz" wrote in message
...
Hi Excel experts

I working on this code and i don't know why no run well.

I have a lot data in the Current region

Option Explicit

Sub Test()
Dim myrange As Range
Dim mycell As Range
Set myrange = Range("B2").CurrentRegion.Columns(1)
For Each mycell In myrange
mycell.Select
Next mycell
End Sub

Thanks for your expert advice


Regards


Fernando

Trouble with columns and for each
 

Or to avoid unnecessary selecting

Dim myrange As Range
Dim mycell As Range
Set myrange = Range("B2", Range("B2").End(xlDown))
'myrange.Select
For Each mycell In myrange.Cells
mycell.Select
Next mycell

Not tested,
Alan Beban

Bob Phillips wrote:
Hi Fernando,

The problem is caused by the fact that you are selecting just the column 1
within the currentregion. Thus, myCell is being treated as a column range,
and so there is only one instance of myCell in myRange, they are the same
range, so the loop only iterates once.

Try this alternate version

Dim myrange As Range
Dim mycell As Range
Set myrange = Range("B2", Range("B2").End(xlDown))
myrange.Select
For Each mycell In myrange
mycell.Select
Next mycell

Trouble with columns and for each
 

Oops

That was something I threw in when testing, should have removed it before
posting.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Alan Beban" wrote in message
...
Or to avoid unnecessary selecting

Dim myrange As Range
Dim mycell As Range
Set myrange = Range("B2", Range("B2").End(xlDown))
'myrange.Select
For Each mycell In myrange.Cells
mycell.Select
Next mycell

Not tested,
Alan Beban

Bob Phillips wrote:
Hi Fernando,

The problem is caused by the fact that you are selecting just the column
1
within the currentregion. Thus, myCell is being treated as a column
range,
and so there is only one instance of myCell in myRange, they are the
same
range, so the loop only iterates once.

Try this alternate version

Dim myrange As Range
Dim mycell As Range
Set myrange = Range("B2", Range("B2").End(xlDown))
myrange.Select
For Each mycell In myrange
mycell.Select
Next mycell

Trouble with columns and for each
 

Experts:

Thanks a lot for your help

Regards,

Fernando Ortiz

"Fernando Ortiz" escribió en el mensaje
...
Hi Excel experts

I working on this code and i don't know why no run well.

I have a lot data in the Current region

Option Explicit

Sub Test()
Dim myrange As Range
Dim mycell As Range
Set myrange = Range("B2").CurrentRegion.Columns(1)
For Each mycell In myrange
mycell.Select
Next mycell
End Sub

Thanks for your expert advice


Regards


Fernando