Selection of the range filled in

  • Thread starter Thread starter Kaj Pedersen
  • Start date Start date
K

Kaj Pedersen

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
 
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)
 
Why not just define the range
insert>name>define>put 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]
 
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
 
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
 
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.
 
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
 
Back
Top