How to specify a range?

[Damien McBain]

In this line of code I want to analyse each cell in the range including A2
to the last non-blank cell in column A:

For Each rcd In Worksheets("Data").Range("A2", Selection.End(xlDown))

How do I specify this range without selecting anything?

I've tried (apart from what's above) .Range("A2", Range("A:A").End(xlDown))
which also doesn't work

Any help greatly appreciated

cheers,

Damien

 

[Damien McBain]

In this line of code I want to analyse each cell in the range including A2
to the last non-blank cell in column A:

For Each rcd In Worksheets("Data").Range("A2", Selection.End(xlDown))

How do I specify this range without selecting anything?

I've tried (apart from what's above) .Range("A2", Range("A:A").End(xlDown))
which also doesn't work

Here's the whole thing:

Sub ProcessData()
=================================
For Each rt In Worksheets("Data").Range("A2", Range("A2").End(xlDown))

With Worksheets.Add
.Name = rt
End With
For Each rcd In Worksheets("Data").Range("A2", _
Range("A2").End(xlDown))
If rcd.Value = rt.Value Then
rcd.EntireRow.Copy
Worksheets(rt).Range("A2", Selection.End(xlDown) + 1).Paste
Else
End If
Next rcd

Next rt
End Sub
=================================

It runs fine up till the line after the first "End With". Is the problem my
range selection statement or the way I'm nesting the For...Next?

 

[Jean-Yves]

Voila

For Each rcd In Worksheets("Data").Range("A2",
Worksheets("Data").Range("A2").End(xlDown))
Regards
JY

 

[Jean-Yves]

In : For Each rt In Worksheets("Data").Range("A2", Range("A2").End(xlDown))
Worksheets("Data").Range("A2" : Refer to A2 on Worksheets("Data")
Range("A2").End(xlDown) Refer to all cell in column A on the active sheet,.
ie new wk RT

Therfor you have to repeat the sheet name : Worksheets("Data").Range("A2",
Worksheets("Data").Range("A2").End(xlDown))
Regards
JY

 

[Damien McBain]

In : For Each rt In Worksheets("Data").Range("A2", Range("A2").End(xlDown))
Worksheets("Data").Range("A2" : Refer to A2 on Worksheets("Data")
Range("A2").End(xlDown) Refer to all cell in column A on the active sheet,.
ie new wk RT

Therfor you have to repeat the sheet name : Worksheets("Data").Range("A2",
Worksheets("Data").Range("A2").End(xlDown))
Regards
JY

Cheers JY, thanks to you that part of the code is working now. The bit I'm
having trouble with now is pasting into the new sheet. I need to paste into
the first empty cell in column A.

Would you mind filling in the blanks here?:

Worksheets(rt).<the first empty cell in column a>.Paste

thanks again,

Damien

 

[Jean-Yves]

Voila,
Worksheets(rt).range("A2").End(xlDown).Offset(1,0)
Regards
JY