How to specify a range?

  • Thread starter Thread starter Damien McBain
  • Start date Start date
D

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 said:
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
Click to expand...

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?
 
Voila

For Each rcd In Worksheets("Data").Range("A2",
Worksheets("Data").Range("A2").End(xlDown))
Regards
JY
 
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
 
Jean-Yves said:
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
Click to expand...

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
 
Back
Top