Selection of the range filled in
 

Hi,
I need a macro that can select a range.

It should always start in B3 and continue with the cells below until a blank
cell is met.

The selection should be used later for sorting or copying.



I hope someone can help.



Regards

Kaj Pedersen

Selection of the range filled in
 

Kaj,

cRows= Cells(Rows.Count,"B").End(xlUp).Row-1
Range("B3:B"&cRows).Select

This searches from the bottom to the first encountered non-blank cell, so if
you have a blank in say row 20, and then data in row 21, it will include row
21. Reading your post literally, this may not be what you want.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Kaj Pedersen" wrote in message
...
Hi,
I need a macro that can select a range.

It should always start in B3 and continue with the cells below until a
blank
cell is met.

The selection should be used later for sorting or copying.



I hope someone can help.



Regards

Kaj Pedersen

Selection of the range filled in
 

Why not just define the range
insertnamedefineput in a name such as rngB
in the refers to box type in
=offset($b$3,0,0,counta($B:$B),1)
now sort on [rngB]

"Kaj Pedersen" wrote in message
...
Hi,
I need a macro that can select a range.

It should always start in B3 and continue with the cells below until a
blank
cell is met.

The selection should be used later for sorting or copying.



I hope someone can help.



Regards

Kaj Pedersen

Selection of the range filled in
 

Hi Bob,
Your suggestion was exactly what I wanted, except that I have not used -1 in
the first line.
Thank you.

Regards
Kaj Pedersen

Selection of the range filled in
 

Kaj,

That's because I assumed something that you didn't say, that is you wanted
to stop at the last data row, not the first blank row. I should have made
that clear to you.

Glad it works for you.

Bob

"Kaj Pedersen" wrote in message
...
Hi Bob,
Your suggestion was exactly what I wanted, except that I have not used -1
in
the first line.
Thank you.

Regards
Kaj Pedersen

Selection of the range filled in
 

Bob,
think you made a mistake with the -1: (mental glitch)


Range("B20").Value = 1
? cells(rows.Count,2).end(xlup).Row
20

It doesn't stop until it gets to the filled cell - you know that.

--
Regards,
Tom Ogilvy



Bob Phillips wrote in message
...
Kaj,

That's because I assumed something that you didn't say, that is you wanted
to stop at the last data row, not the first blank row. I should have made
that clear to you.

Glad it works for you.

Bob

"Kaj Pedersen" wrote in message
...
Hi Bob,
Your suggestion was exactly what I wanted, except that I have not
used -1
in
the first line.
Thank you.

Regards
Kaj Pedersen

Selection of the range filled in
 

Tom,

You're absolutely right. I (think that I) was getting confused with the next
free cell. So it wasn't making a maybe invalid assumption, just a simple
stupid error.

Things get far too damned complex, especially when you start analysing what
you did and why you did<VBG.

Think I'll just stick to drinking the wine, and be happy that the OPs get
what they want, especially when the figure it themselves.

Best Regards

Bob


"Tom Ogilvy" wrote in message
...
Bob,
think you made a mistake with the -1: (mental glitch)


Range("B20").Value = 1
? cells(rows.Count,2).end(xlup).Row
20

It doesn't stop until it gets to the filled cell - you know that.

--
Regards,
Tom Ogilvy



Bob Phillips wrote in message
...
Kaj,

That's because I assumed something that you didn't say, that is you
wanted
to stop at the last data row, not the first blank row. I should have
made
that clear to you.

Glad it works for you.

Bob

"Kaj Pedersen" wrote in message
...
Hi Bob,
Your suggestion was exactly what I wanted, except that I have not
used -1
in
the first line.
Thank you.

Regards
Kaj Pedersen