Selecting The Last Row

[Minitman]

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

 

[james.billy]

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

 

[Bob Phillips]

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

 

[Jef Gorbach]

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

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

 

[davidm]

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

 

[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

 

[Minitman]

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

 

[Minitman]

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

 

[Minitman]

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

 

[Bob Phillips]

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

or

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

although you shouldn't be selecting.

 

[Minitman]

Hey Bob,

That looks good.

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

-Minitman

 

[Bob Phillips]

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.

 

[Minitman]

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

 

[Bob Phillips]

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.

 

[Minitman]

Hey Bob,

Thanks, I'll give that a try

-Minitman