Greeting,

I need to select columns A thru W on the last filled row. I know that
Range("A65536").End(xlUp) will get me to the first cell of the last
row, but how do I get from first cell to A thru W?

Any help would be appreciated.

TIA

-Minitman

Hi,

You could do something like setting the last row to a variable and then
using that. For example:

Endrow = Range("A65536").end(xlup).row
Range("A2:W" & Endrow).Select

Another way to do it is to select the current region for example:

Range("A2").CurrentRegion.select

The current region is the same as the short cut key ctrl + *.

Both of the examples assume your data starts in cell A2.

Any problems then give me a shout.

James

Hey James,

My data starts not in A2 but in A<LastRow.

It looks like Bob's solution might be a bit better for what I am
trying to do, which is add two more conditions without using CFPlus to
change those few rows that need them.

Thanks for the assistance

-Minitman


On 19 Jul 2005 20:56:44 -0700, wrote:

Hi,

You could do something like setting the last row to a variable and then
using that. For example:

Endrow = Range("A65536").end(xlup).row
Range("A2:W" & Endrow).Select

Another way to do it is to select the current region for example:

Range("A2").CurrentRegion.select

The current region is the same as the short cut key ctrl + *.

Both of the examples assume your data starts in cell A2.

Any problems then give me a shout.

James

Function LastRow(rng As Range)
On Error Resume Next
LastRow = 1
With rng
LastRow = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
End With
End Function


Call it like

myLastRow = LastRow(Range("A:W"))

--
HTH

Bob Phillips

"Minitman" wrote in message
...
Greeting,

I need to select columns A thru W on the last filled row. I know that
Range("A65536").End(xlUp) will get me to the first cell of the last
row, but how do I get from first cell to A thru W?

Any help would be appreciated.

TIA

-Minitman

Hey Bob,

One question, what is the correct syntax to select
Range(A<LastRow:W<LastRow).Select
I've tried a few ways and I keep getting

Run-time error '1004':
Method 'Range' of object '_Global' failed

What do I need to change to make this work - I'm at a loss!

Other then that, it looks like it should work :^

-Minitman


On Wed, 20 Jul 2005 09:56:47 +0100, "Bob Phillips"
wrote:

Function LastRow(rng As Range)
On Error Resume Next
LastRow = 1
With rng
LastRow = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
End With
End Function


Call it like

myLastRow = LastRow(Range("A:W"))

Range("A" & LastRow & ":W" & LastRow).Select

or

Range("A" & LastRow).Resize(,23).Select

although you shouldn't be selecting.

--
HTH

Bob Phillips

"Minitman" wrote in message
...
Hey Bob,

One question, what is the correct syntax to select
Range(A<LastRow:W<LastRow).Select
I've tried a few ways and I keep getting

Run-time error '1004':
Method 'Range' of object '_Global' failed

What do I need to change to make this work - I'm at a loss!

Other then that, it looks like it should work :^

-Minitman


On Wed, 20 Jul 2005 09:56:47 +0100, "Bob Phillips"
wrote:

Function LastRow(rng As Range)
On Error Resume Next
LastRow = 1
With rng
LastRow = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
End With
End Function


Call it like

myLastRow = LastRow(Range("A:W"))

Hey Bob,

That looks good.

You mentioned that Selecting was the wrong route to go, what would you
suggest?

-Minitman



On Wed, 20 Jul 2005 18:10:22 +0100, "Bob Phillips"
wrote:

Range("A" & LastRow & ":W" & LastRow).Select

or

Range("A" & LastRow).Resize(,23).Select

although you shouldn't be selecting.

What I mean is that selecting is inefficient and also difficult to read as
you try to follow code.

Take this example

Range("A1").Select
Do
If Activecell.Value 17 Then
Activecell.Offset(0,10).Value = "Valid"
End If
Activecell.Offset(1,0).Select
Loop Until Activecell.value = ""

All that selecting is keeping the system very busiy, adjusting its pointers,
re-displaying the activecell, etc.

This can be written as

For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row
If Cells(i,"A").Value 17
Then Cells(i,11).Value = "Valid"
End If
Next i

No selecting, more efficient, easier to raed (IMO).

Of coures this is a simple example, it gets more relevant in big, complex
code.

--
HTH

Bob Phillips

"Minitman" wrote in message
...
Hey Bob,

That looks good.

You mentioned that Selecting was the wrong route to go, what would you
suggest?

-Minitman



On Wed, 20 Jul 2005 18:10:22 +0100, "Bob Phillips"
wrote:

Range("A" & LastRow & ":W" & LastRow).Select

or

Range("A" & LastRow).Resize(,23).Select

although you shouldn't be selecting.

"Minitman" wrote in message
...
Greeting,

I need to select columns A thru W on the last filled row. I know that
Range("A65536").End(xlUp) will get me to the first cell of the last
row, but how do I get from first cell to A thru W?

Any help would be appreciated.

TIA

-Minitman

range("A65536").end(xlup).entirerow.select

He is asking to get the last row in any columns A-W.

--
HTH

Bob Phillips

"Jef Gorbach" wrote in message
...

"Minitman" wrote in message
...
Greeting,

I need to select columns A thru W on the last filled row. I know that
Range("A65536").End(xlUp) will get me to the first cell of the last
row, but how do I get from first cell to A thru W?

Any help would be appreciated.

TIA

-Minitman

range("A65536").end(xlup).entirerow.select

Hey Jef,

Thanks for the reply

This looks good,except I don't want the entire row, just A thru W on
that row.

-Minitman


On Wed, 20 Jul 2005 09:08:39 -0400, "Jef Gorbach"
wrote:


"Minitman" wrote in message
.. .
Greeting,

I need to select columns A thru W on the last filled row. I know that
Range("A65536").End(xlUp) will get me to the first cell of the last
row, but how do I get from first cell to A thru W?

Any help would be appreciated.

TIA

-Minitman

range("A65536").end(xlup).entirerow.select

Bob,

Could you please explain how the formula

LastRow = MyRng.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row

tracks the last row? On the face of it, I would intuitively interpret
it to be "Move down the column starting from 1st cell, checking throug
formulas and whole values row-by-row for as long as data is found; sto
if data is not found". But upon testing, this representation flies i
the face of the (correct)results. For instance where there are blank
in the column, the formula skips these blanks and rightly locate th
last cell in column. Again, it does not really need "formulas" to be i
the range!

Just curious

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=38856

Hey David,

Good point and a fact that I forgot to mention, there may or may not
be data in the cells in column A. There will always be data in the
cells of either column A or B or both. If there is no data in the
cells of either column then that row is blank.

I hope this explains the situation a little better.

-Minitman


On Wed, 20 Jul 2005 10:40:19 -0500, davidm
wrote:


Bob,

Could you please explain how the formula

LastRow = MyRng.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row

tracks the last row? On the face of it, I would intuitively interprete
it to be "Move down the column starting from 1st cell, checking through
formulas and whole values row-by-row for as long as data is found; stop
if data is not found". But upon testing, this representation flies in
the face of the (correct)results. For instance where there are blanks
in the column, the formula skips these blanks and rightly locate the
last cell in column. Again, it does not really need "formulas" to be in
the range!

Just curious.