How do I select a range using VBA?
 

Hi,

wondered if any of you kind souls could help me?

I'm writing a macro to take certain columns (containg last
name, first name and ID number) from one worksheet and
pastes them into another. This "table" is then sorted by
ID number, although some of the cells don't contain
numbers and the sort places them at the bottom end of the
list, so far so good.

I've got a sub to search through the ID number list until
it finds the first non-numeric entry and I want to then
create a selection from that cell back to the first cell
of the table (C4 in this case) and would imagine it's
something like

Range("currentcell:C4").Select

but for some reason I can't work out how to do this, and
it's probably so simple that I'll kick myself!

Oh, by the way, this procedure is to be run weekly and the
cell address of the first non-numeric entry will change
every time which is why i can't just specify a cell
address.

Help!

Thanks,

H.

How do I select a range using VBA?
 

Hi
in nearly all cases a Select is not required.
e.g. instead of something like
Range("A1:D1").select
selection.copy

you could write
Range("A1:D1").copy


But if you wa<nt to select something and you want to
select everything from C1 to your activecell one way would
be:
Range("C1:C" & activecell.row).select

You may post your current code if you like. There may be
better ways without using select statements :-)

-----Original Message-----
Hi,

wondered if any of you kind souls could help me?

I'm writing a macro to take certain columns (containg
last
name, first name and ID number) from one worksheet and
pastes them into another. This "table" is then sorted by
ID number, although some of the cells don't contain
numbers and the sort places them at the bottom end of the
list, so far so good.

I've got a sub to search through the ID number list until
it finds the first non-numeric entry and I want to then
create a selection from that cell back to the first cell
of the table (C4 in this case) and would imagine it's
something like

Range("currentcell:C4").Select

but for some reason I can't work out how to do this, and
it's probably so simple that I'll kick myself!

Oh, by the way, this procedure is to be run weekly and
the
cell address of the first non-numeric entry will change
every time which is why i can't just specify a cell
address.

Help!

Thanks,

H.
.

How do I select a range using VBA?
 

Howard,

It's as simple as this:

Range(activecell,"c4").Select

However, you don't need to select the range, but can use

Range(activecell,"c110").copy

And before you get too hooked on the activecell thing, you might be better
off with something like

Range(RANGE("C4").End(xlDown),"C4").Copy

In general terms, code that actually selects things runs massively slower
than code that just references a particular range. If you use the macro
recorder you see the selection, e.g.

range("a1").select
selection.font.bold = true

efficient code would read

range("a1").font.bold = true

HTH,

Robin Hammond
www.enhanceddatasystems.com

"Howard" wrote in message
...
Hi,

wondered if any of you kind souls could help me?

I'm writing a macro to take certain columns (containg last
name, first name and ID number) from one worksheet and
pastes them into another. This "table" is then sorted by
ID number, although some of the cells don't contain
numbers and the sort places them at the bottom end of the
list, so far so good.

I've got a sub to search through the ID number list until
it finds the first non-numeric entry and I want to then
create a selection from that cell back to the first cell
of the table (C4 in this case) and would imagine it's
something like

Range("currentcell:C4").Select

but for some reason I can't work out how to do this, and
it's probably so simple that I'll kick myself!

Oh, by the way, this procedure is to be run weekly and the
cell address of the first non-numeric entry will change
every time which is why i can't just specify a cell
address.

Help!

Thanks,

H.

How do I select a range using VBA?
 

Thanks Frank, I'll give it a whirl.

Cheers,

H.

How do I select a range using VBA?
 

To select your range: (sub with your specifics)

Windows("Source Worksheet.xls").Activate
range("A1:K21").Select
Selection.Copy
Windows("Destination Workbook.xls").Activate
range("A1").Select
ActiveSheet.Paste



----Original Message-----
Hi,

wondered if any of you kind souls could help me?

I'm writing a macro to take certain columns (containg
last
name, first name and ID number) from one worksheet and
pastes them into another. This "table" is then sorted by
ID number, although some of the cells don't contain
numbers and the sort places them at the bottom end of the
list, so far so good.

I've got a sub to search through the ID number list until
it finds the first non-numeric entry and I want to then
create a selection from that cell back to the first cell
of the table (C4 in this case) and would imagine it's
something like

Range("currentcell:C4").Select

but for some reason I can't work out how to do this, and
it's probably so simple that I'll kick myself!

Oh, by the way, this procedure is to be run weekly and
the
cell address of the first non-numeric entry will change
every time which is why i can't just specify a cell
address.

Help!

Thanks,

H.
.