vba select a range - known start variable end

Microsoft Ms sel office xp pro/frontpage...
March 24, 2010 at 17:01:41
Specs: windows xp
Macro statement to select a range from "F4" to an activecell. Active cell variable row on column "K" found by
Range ("L9").Select 'column "L" will have marker
selection.end(x1down).select
range(activecell.offset(0,-1).select
I need to daily copy this expanding range (f4:activecell) for further processing. Your help would be most appreciated. Thank you.

See More: vba select a range - known start variable end

Report •


#1
March 25, 2010 at 05:45:02
Have you tried this?

Range("F4:K" & Range("L9").End(xlDown).Row).Copy

Note: Rarely do you have to "Select" a range in VBA to perform an action on it. Selecting ranges as you are is inefficient since you can perform most actions directly within VBA.


Report •

#2
March 26, 2010 at 03:14:04
Great! That worked exceedingly well. Thank you for taking the time to share your knowledge.

Report •

#3
March 26, 2010 at 08:06:42
I'm glad I could help.

BTW...sticking with the "no need to Select ranges" theme, that line can be extended to read something like this to perform the Copy/Paste in one smooth move:

Range("F4:K" & Range("L9").End(xlDown).Row).Copy _
   Destination:= Sheets(2).Range("A1")


Report •

Related Solutions

#4
March 26, 2010 at 08:25:18
Wow!!! Thank you. I wish you were my daddy :-)

Report •

#5
March 26, 2010 at 09:32:03
re: I wish you were my daddy

How old are you? It's tax season and I could use another dependent.


Report •

#6
March 26, 2010 at 09:58:00
lol. Probably old enough to be your grandfather. 64years.
How rare and nice to engage with a sense of humour and a willingness to share their knowledge. I did note and agree that in some topics the posters could be more humble in their requests. After all jewel like yourself is so hard to find even with the mighty google!

Report •

#7
March 26, 2010 at 11:32:10
re: "Probably old enough to be your grandfather"

Not even old enough to be my father...closer to an older brother.

Thanks for the compliments and I'm glad to see an "older gentleman" still using the gray matter and trying to learn new stuff.

Come on back the next time you need some help.


Report •

#8
March 26, 2010 at 12:56:24
Hi bribin,


Stop calling him a jewel - these compliments always go to his head, and he will be even worse to deal with.

Regards

Humar


Report •

#9
March 26, 2010 at 13:52:46
I find him very courteous and most deserving of the accolade!

Report •

#10
March 26, 2010 at 17:56:37
Me too!

Report •

#11
March 27, 2010 at 04:05:11
...
You see ... It's started already!

Report •

#12
March 28, 2010 at 05:45:47
OK I'll settle this and show you how good he is! Just watch DerbyDad03 solve these posers for me. Columns ("G:I") each contain formula to process data from columns ("A:C"). The Rows of ("G:I") extend beyond the current input of data in Rows ("A:C") as they await daily input. Column ("B") is the only column to have data in every row. Using End(xlDown) on column ("B") will identify the most recent entry BUT Poser 1. I would like to copy the range ("F4") to Column("K") on the Row ("B").end(xlDown) to Sheets("2").Range("G10"). Poser 2. With the range in Sheets("2") still selected I would like to Sort on column ("H")

Report •

#13
March 28, 2010 at 08:08:23
re: Poser 1. I would like to copy the range ("F4") to Column("K") on the Row ("B").end(xlDown) to Sheets("2").Range("G10")

I'm pretty sure I told you every thing you need to know to do this.

Just change the pertinent portions of the line I posted in Response #3:

Range("F4:K" & Range("L9").End(xlDown).Row).Copy _
   Destination:= Sheets(2).Range("A1")

As Marine Gunny Highway (Clint Eastwood) once said "Improvise, adapt and overcome."

re: Poser 2. With the range in Sheets("2") still selected I would like to Sort on column ("H")

Once again, we are not going to Select any ranges. We can sort it directly without Selecting it.

'Determine last Row of Pasted range
'The copy started in Row 4 but was pasted into
'Row 10, so the last Row of the pasted Range
'is 6 Rows farther down than the last Row of 
'data in Sheet1 Column B 
     rngRow = Sheets(1).Range("B9").End(xlDown).Row + 6
'Sort the pasted range, keyed on Column H
        Sheets(2).Range("G10:L" & rngRow).Sort _
          Key1:=Sheets(2).Range("H10")


Report •

#14
March 28, 2010 at 16:15:42
HA! see Humar I told you! Sit up and take your medicine from a master - DerbyDad03 - He is tops of the pops for me!
Thank you DerbyDad03.

Report •

#15
March 28, 2010 at 18:23:56
Whoa! Hold on there bribin.

There is no competition in this forum and I'm certainly no "master".

You may not realize it, but what you are asking for is not extremely advanced and Humar, as well as many others in this forum, could have given you a solution.

I was simply there when you needed help, and the others let me run with it. At any time they could have jumped in and offered similar code. They were just being polite.

Good natured ribbing is fine, but be careful how you word it - you might just need help from someone else in the very near future.


Report •

#16
March 29, 2010 at 04:11:38
It is I who am humbled. Let it be known that it was never my intention to cause malice or hurt. My response was in a playful manner and was; I now realise, thoughtless in the wider scheme of things.

To Humar I offer my unreserved apologies and trust that any friction caused by my comments will swiftly be laid to rest.

To DerbyDad03 I also apologise. As a novice user I have overstepped the code of mutual respect which exists among contributors.

To forum users in general please forgive my lapse.


Report •

#17
March 29, 2010 at 04:28:56
Hello bribin,

Laid to rest - absolutely, and thank you for your response.

Regards

Humar


Report •

#18
March 29, 2010 at 06:31:53
Same Same

Come on back whenever you need some help.


Report •

#19
March 31, 2010 at 08:38:33
File Name = “##########.PPSTRAN.########.####”
set 1.(#) unchanging numbers
set 2.(#) changes daily - date format ‘yyyymmdd’
set 3.(#) changes daily - consecutively 0000 thro 9999

I use a macro in “FileViewer.xls” to process this file and generate a new single sheet workbook with the data output in my preferred format. During the run of the macro my ‘master’ file is opened and thanks to code you provided my formatted data is appended to the ‘master’ file. It is immediately saved and remains my active window as it will be used throughout the day.
I now have 3 workbooks open, 2 of which have served their purpose. I would like to tidy things by closing “FileViewer.xls” and also saving and closing “##########.PPSTRAN.########.####”.
Ps. I backup daily - My mummy insists it’s a good thing to do, well that and putting on clean underwear before I go out!


Report •

#20
March 31, 2010 at 08:51:33
Hi,

At the end of your macro you should be able to add a line like this:

Workbooks("FileViewer.xls").Close SaveChanges:=True

and a second one for the other file to be closed.
If you don't need to save, use:
Workbooks("A_N_Other.xls").Close SaveChanges:=False

Regards


Report •

#21
April 30, 2010 at 10:16:27
Thank you
Sorry for delay Easter and one thing or another.....

Report •


Ask Question