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

JohnRussell

New Member
Joined
Jun 28, 2013
Messages
2
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

Board Regular
Joined
Sep 10, 2009
Messages
134
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
He's pasting the range in, so he already knows the first cell to start with to work downwards.
 

mwrohr

Board Regular
Joined
Sep 10, 2009
Messages
134
fair enough, apologies for coming off like a jerk!
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

New Member
Joined
Jun 28, 2013
Messages
2
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.
 
Last edited:

Forum statistics

Threads
1,081,726
Messages
5,360,906
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top