Adding a column to a selection in Excel (2000) vba

[JohnRussell]

Hi,

Not much experience in vba.

My script ends by copying a filtered range and then pasting the (special paste values) further down the page. When the script ends the pasted range is still selected. I want to continue my script by adding a column of analysis to the right of this pasted range. For instance it might be the sum of the rows.
But how do I reference this added column? I know how to reference the beginning, but not the end, because that will depend on the results of the filtering, which will be different every time.

 

[ChrisM]

There's lots of ways to go about this. Here are a couple tips:

range("a10").End(xlDown).Select
range("a10").resize(range("a10").End(xlDown).Row -10+1,1).select
range("a10").resize(range("a10").End(xlDown).Row -10+1,1).Offset(0,1).Select

Since you know where you pasted in ("A10") in my case, you just need to figure out how far down your data goes.
The first command will select the last used cell below A10.
Now say you want to select the whole range you just pasted. The second command will do that (note the 10 I used after Row, you will have to change this to your row #)
And finally, say you want to select the column to the right. The third row will do that.

You should get familiar with those three functions, End, Resize, and Offset. They are very helpful.

 

[mwrohr]

ChrisM, that's well and good except if "A10" is the last cell of the range; best way would be to come up from the bottom

range("A65000").end(xlup).offset(1,0).select

 

[ChrisM]

He's pasting the range in, so he already knows the first cell to start with to work downwards.

 

[mwrohr]

fair enough, apologies for coming off like a jerk!

 

[ChrisM]

You're right though, working from bottom to top is certainly the normal method. I just thought this was a special case since he knows where the starting cell is.

 

[JohnRussell]

There's lots of ways to go about this. Here are a couple tips:

range("a10").End(xlDown).Select
range("a10").resize(range("a10").End(xlDown).Row -10+1,1).select
range("a10").resize(range("a10").End(xlDown).Row -10+1,1).Offset(0,1).Select

Since you know where you pasted in ("A10") in my case, you just need to figure out how far down your data goes.
The first command will select the last used cell below A10.
Now say you want to select the whole range you just pasted. The second command will do that (note the 10 I used after Row, you will have to change this to your row #)
And finally, say you want to select the column to the right. The third row will do that.

You should get familiar with those three functions, End, Resize, and Offset. They are very helpful.

Many thanks for that. There was a slight problem with the second two of your suggestions: -
I was copying and pasting the results of a filter. The size of this past was variable, so I was pasting to a single cell rather than a range. Under the circumstances the second two of your suggestions selected to the end of the sheet rather than the end of the pasted range. My solution was as follows: -

Application.Goto Reference:="Grouppaste"
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Offset(0, 7).Select
ActiveSheet.Paste

"Grouppaste" is my single cell paste reference.

Thanks again.