Trouble with columns and for each

[Fernando Ortiz]

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

 

[Bob Phillips]

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)

 

[A.W.J. Ales]

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 *

 

[Alan Beban]

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]

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)

 

[Fernando Ortiz]

Experts:

Thanks a lot for your help

Regards,

Fernando Ortiz