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

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

"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

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 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 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

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

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.

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.

Hey Bob,

So what you are saying is, Select will work but not efficiently. I am
still fairly new at programming and am always looking for the most
efficient way to write the code. Can you recommend any GOOD books or
classes on the subject?

I finally got this to work from the UserForm paste down, but that does
not answer the problem of all the entry that is already there. That
is where the CF worked well. Oh well, I guess a few hours of manually
fixing the old formats one time is better then taking a few days to do
it automatically. <G

There is still a LOT of data to be entered.

Thanks for the assistance, it really helped.

-Minitman


On Thu, 21 Jul 2005 09:50:00 +0100, "Bob Phillips"
wrote:

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.

Yes, I am saying that. But ... although it may work, it is more error prone
as it is more difficult IMO to know where you are, rather than using an
index within a loop. Also, it is much harder to amend later, for similar
reasons.

As to a book, depends upon where your current skill level is, but I would
suggest you go down to your local bookstore at look at John Walkenbach's VBA
for Dummies, see if you think it will help.

--
HTH

Bob Phillips

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

So what you are saying is, Select will work but not efficiently. I am
still fairly new at programming and am always looking for the most
efficient way to write the code. Can you recommend any GOOD books or
classes on the subject?

I finally got this to work from the UserForm paste down, but that does
not answer the problem of all the entry that is already there. That
is where the CF worked well. Oh well, I guess a few hours of manually
fixing the old formats one time is better then taking a few days to do
it automatically. <G

There is still a LOT of data to be entered.

Thanks for the assistance, it really helped.

-Minitman


On Thu, 21 Jul 2005 09:50:00 +0100, "Bob Phillips"
wrote:

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.

Hey Bob,

Thanks, I'll give that a try

-Minitman



On Thu, 21 Jul 2005 18:23:24 +0100, "Bob Phillips"
wrote:

Yes, I am saying that. But ... although it may work, it is more error prone
as it is more difficult IMO to know where you are, rather than using an
index within a loop. Also, it is much harder to amend later, for similar
reasons.

As to a book, depends upon where your current skill level is, but I would
suggest you go down to your local bookstore at look at John Walkenbach's VBA
for Dummies, see if you think it will help.