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

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" wrote:

Sub lastrow()
ActiveCell.End(xlUp).Select
End Sub

"Greg Snidow" wrote:

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 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" wrote:

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" wrote:

Sub lastrow()
ActiveCell.End(xlUp).Select
End Sub

"Greg Snidow" wrote:

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

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.

"Greg Snidow" wrote:

Ok, what it does is select vertically, the last cell with data above the
active cell in the same column. I need it to select the last cell in the
active row, or move horizontally to right, to the last cell with data. Does
that make more sense?

"Mike" wrote:

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" wrote:

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" wrote:

Sub lastrow()
ActiveCell.End(xlUp).Select
End Sub

"Greg Snidow" wrote:

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

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

"Greg Snidow" wrote:

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.

"Greg Snidow" wrote:

Ok, what it does is select vertically, the last cell with data above the
active cell in the same column. I need it to select the last cell in the
active row, or move horizontally to right, to the last cell with data. Does
that make more sense?

"Mike" wrote:

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" wrote:

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" wrote:

Sub lastrow()
ActiveCell.End(xlUp).Select
End Sub

"Greg Snidow" wrote:

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

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" wrote:

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.

"JLGWhiz" wrote:

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
On Error GoTo 0
End Function

"Greg Snidow" wrote:

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

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.

"JLGWhiz" wrote:

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
On Error GoTo 0
End Function

"Greg Snidow" wrote:

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

"Greg Snidow" wrote:

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.

"JLGWhiz" wrote:

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
On Error GoTo 0
End Function

"Greg Snidow" wrote:

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

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
On Error GoTo 0
End Function

"Greg Snidow" wrote:

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

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

"Mike" wrote:

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

"Greg Snidow" wrote:

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.

"JLGWhiz" wrote:

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
On Error GoTo 0
End Function

"Greg Snidow" wrote:

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

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

"Mike" wrote:

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

"Greg Snidow" wrote:

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.

"Greg Snidow" wrote:

Ok, what it does is select vertically, the last cell with data above the
active cell in the same column. I need it to select the last cell in the
active row, or move horizontally to right, to the last cell with data. Does
that make more sense?

"Mike" wrote:

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" wrote:

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" wrote:

Sub lastrow()
ActiveCell.End(xlUp).Select
End Sub

"Greg Snidow" wrote:

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

That one worked like a charm, thank you.

"Mike" wrote:

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

"Greg Snidow" wrote:

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.

"Greg Snidow" wrote:

Ok, what it does is select vertically, the last cell with data above the
active cell in the same column. I need it to select the last cell in the
active row, or move horizontally to right, to the last cell with data. Does
that make more sense?

"Mike" wrote:

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" wrote:

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" wrote:

Sub lastrow()
ActiveCell.End(xlUp).Select
End Sub

"Greg Snidow" wrote:

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

Try

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Greg Snidow" wrote in message ...
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