Selecting a range of cells, how to ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Luc - 23 Feb 2007 19:03 GMT

Hello,

I have a problem with selecting cells.

The selection allways goes from column A to M.
The number of rows starts allways from A 11, to the LAST CELL in column A
which contains a number
So the column range is fix, and the row range is variable.

For example :
It can be A11 to M24
or A11 to M5
or A11 to M11 (this is only 1 row)

What code should i use in the macro ?

Thanxx

Luc

--------------------------------------------------------------------------------
Mijn Postvak In wordt beschermd door SPAMfighter
301 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!

Reply to this Message

Jason Lepack - 23 Feb 2007 19:14 GMT

Assuming that there are no gaps between numbers in column A then this
will work:

Public Sub selectStuff()
Dim ws As Worksheet
Set ws = ActiveSheet
if not ws.range("A12") = "" then
ws.Range("A11", ws.Cells(ws.Range("A11").End(xlDown).Row,
13)).Select
else
ws.range("A11:M11").select
end if
End Sub

' ws.Range("A11").End(xlDown).Row
' gets the row number of the last number before a space moving
downward from A11.
' However if A12 is blank then it would go to row 65536, therefore the
if statement

Cheers,
Jason Lepack

> Hello,
>
[quoted text clipped - 20 lines]
> 301 spam-mails zijn er tot op heden geblokkeerd.
> Download de gratis SPAMfighter vandaag nog!

Reply to this Message

Chip Pearson - 23 Feb 2007 19:17 GMT

Try code like the following:

Dim LastRow As Long
With ActiveSheet
LastRow = Application.WorksheetFunction.Max(11, _
.Cells(Rows.Count, "A").End(xlUp).Row)
.Range(.Cells(11, "A"), .Cells(LastRow, "M")).Select
End With

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

> Hello,
>
[quoted text clipped - 20 lines]
> 301 spam-mails zijn er tot op heden geblokkeerd.
> Download de gratis SPAMfighter vandaag nog!

Reply to this Message