Last column

[Guest]

Can someone please tell me how to create a line in vba that activates/selects
the last cell with data in the row that I'm in. So if I have a table that
goes from A1 to Z20 and I'm somewhere in row 3 what script do I put in the
macro to get me to Z3. I will be adding a new column of data every month so
next month I'll be adding data to column AA. Then the next month I'll be
adding to AB and so on. Please don't take the impression that it will be row
3 that I'm in every time though. I just need to get to the last cell in the
particular row that I'm in. So if I'm in row 3 I will need to get to the
last cell in row 3 and not the last cell in any other row.

 

[Guest]

Carl,

here's one way:

Cells(ActiveCell.Row, 256).End(xlToLeft).Select

 

[Guest]

Here is a function that I use to determine the last active column in a sheet.
It uses the Find function, searching for a wildcard (*) starting from cell A1
and searching backwards by column. Therefore the first time it finds data
then the column is returned by the function. If you do not wish to include
formulae then change LookIn:=xlFormulas to LookIn:=xlValues. If you use
xlValues then any formula that results in no data - "" will not be found, so
you may overwrite something. You must pass a worksheet to the function, and
it will return the column number as an integer. Finally, you will need the On
Error part, because any empty sheets will throw up an error, so you use the
Error to set the last active column as 0. This function will give the last
column in the sheet, but not necessarily the last column in your particular
row, and I believe this is what you are looking for. This definitely works
for Excel version 11, and should work in most other recent versions.

I hope it helps!

Private Function Last_Column_Temp(ByVal LocalWorksheet As Worksheet) As
Integer
On Error GoTo LastColumnError
Last_Column_Temp = LocalWorksheet.Cells.Find(What:="*", _
after:=ActiveSheet.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
Exit Function
LastColumnError:
On Error GoTo 0
Last_Column_Temp = 0
End Function

 

[Guest]

Sorry, I forgot to mention, to get to the last column in your current row
then call the function using:


ActiveSheet.Cells(ActiveCell.Row, Last_Column_Temp(ActiveSheet)).Select

Now, I hope it helps!

 

[Don Guillett]

Sub lastcol()
lc = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
MsgBox lc
End Sub