How to specify a range?
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 |
How to specify a range?
Damien McBain wrote:
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? |
How to specify a range?
Voila
For Each rcd In Worksheets("Data").Range("A2", Worksheets("Data").Range("A2").End(xlDown)) Regards JY "Damien McBain" wrote in message ... 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 |
How to specify a range?
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" wrote in message ... Damien McBain wrote: 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? |
How to specify a range?
Jean-Yves wrote:
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 |
How to specify a range?
Voila,
Worksheets(rt).range("A2").End(xlDown).Offset(1,0) Regards JY "Damien McBain" wrote in message ... Jean-Yves wrote: 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 |
All times are GMT +1. The time now is 03:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com