Last cell in active row--I know, I'm sorry

[Greg Snidow]

Greetings all. I know this question has been asked here many, many times,
but I can not get any of the examples to do exactly what I need. I need to
select the last cell with data on the acive row. So, for example, if I have
cell A1 selected, and there are data in cells A2, A5, and A8, I need a macro
to select A8. If, then, I select B3, and there are data in B1, B3, and B10,
I need it to then select B10. Any ideas? Thank you, and again, I apologize
for the redundant question.

Greg

 

[Greg Snidow]

Mike, thank you so much for the quick reply so late at night, but maybe it is
not 10:00 for you. I found that snippet here a couple of weeks ago, and it
is awesome, but it finds the last cell in a column. I need to find the last
cell in the row. Any ideas?

 

[Mike]

Greg maybe you need to explain a little better cause what you asked to do the
macro i gave you does.

You said "if I have cell A1 selected, and there are data in cells A2, A5,
and A8, I need a macro to select A8"
Sub lastrow()
ActiveCell.End(xlUp).Select
End Sub

 

[Greg Snidow]

And, I do now realize that you gave me exactly what I asked for. Please
forgive me, I got mixed up. What I need is if I have values in A1, C1, and
H1, and I have any cell in row 1 selected, I need it to activate H1. I am
sorry for the confusion.

 

[Mike]

Try this Greg
Sub lastColumn()
Application.ScreenUpdating = False
Do Until ActiveCell.Column = 256
ActiveCell.End(xlToRight).Select
Loop
ActiveCell.End(xlToLeft).Select
Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

OK Greg, I'll just throw this in as a freebie for getting the last column.
Assumes row 1 will contain a header in the last column. If there is a row
longer that the header row then that should be the one used in the Cells(?,
Columns.Count)

Sub GetLastCol()
Dim lastCol As Long
lastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox lastCol
End Sub

 

[Greg Snidow]

JLGWhiz, I must apologize. I got mixed up in the OP, and the example I
provided indicated that I was looking for the last row, when I actually need
the last column. If you read down the rest of them, Mike was kind enough to
point it out, and stick with it, so I now have two working solutions. Thanks
again.

 

[Mike]

Greg just to simplify your code u can use this
Sub LastCol()
Cells(ActiveCell.Row, 256).End(xlToLeft).Activate
'or
Range("IV" & ActiveCell.Row).End(xlToLeft).Activate
End Sub

 

[JLGWhiz]

Copy this function to your standard module1 in the VBE. Then in the code
that you are writing you would assign the last row variable like:

lstRw = lastRow(Worksheets(1))

Then use lstRw to designate your cell references like:

Cells(lstRw, 1) would be a cell that intersects Column A and last row.

Function lastRow(sh As Worksheet) 'Finds last cell with data in the last
used row.
On Error Resume Next
lastRow = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart,
_ LookIn:=xlFormulas, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious, _ MatchCase:=False).Row

 

[Greg Snidow]

I knew it had to be simpler than I was making it.

 

[Greg Snidow]

Thank you so much for sticking with this Mike. I am going to try your
solution, but before I do, I wanted to let you know that I messed around with
it and came up with a simple solution. I converted cell address to string,
extracted the row, then used xlToLeft to select the last cell in the row.
There are probably better properties I could have used, but I am a SQL guy,
so I fooled around with it until it worked, and I can't tell you how exciting
it was to see it work. There are so many things I don't understand about
Excel coding logic, and it is so much fun learning it. I will post back to
let you know the results of your last post. Here is the solution I came up
with. If you see any shortcomings with it please let me know. Thanks again.

Sub LastCol()

Dim c As String
Dim z As String
Dim CellAddress As String

z = "IV"

CellAddress = ActiveCell.Address
c = Mid(CellAddress, 4, 4)

Range(z & c).End(xlToLeft).Select

End Sub

 

[Greg Snidow]

That one worked like a charm, thank you.

Try this Greg
Sub lastColumn()
Application.ScreenUpdating = False
Do Until ActiveCell.Column = 256
ActiveCell.End(xlToRight).Select
Loop
ActiveCell.End(xlToLeft).Select
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Try

Sub LastCellInOneRow()
Dim LastCol As Long
LastCol = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
Cells(ActiveCell.Row, LastCol).Select
End Sub