Selecting a range of cells, how to ?

[Luc]

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

 

[Jason Lepack]

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

 

[Chip Pearson]

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


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