Last Used Cell in a row??

[Andrius B.]

Hi.
I hope someone could help me this time, too.

I need to find the last used cell (that is, not empty, contains some data)
in a Row and to get the column number, witch contains that cell.

E.G. such rows


person1 | 4556 | | 4564 | | |
person2 | www | | | | dddddd |

So, if I specify a row number 1, the code should return "4", and for the
second row - "6".

I have tried many examples from Internet, but they return either the first
column or number "256"...

Any ideas?

 

[Gary Keramidas]

this may do what you want:

Option Explicit

Sub test()
Dim lastcol As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastcol = ws.Cells(1, 256).End(xlToLeft).Column
MsgBox lastcol
End Sub

 

[Gary Keramidas]

this is more generic

Option Explicit

Sub test()
Dim lastcol As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox lastcol
End Sub

 

[Gary''s Student]

For row #13, for example:

Sub findit()
MsgBox (Cells(13, Columns.Count).End(xlToLeft).Column)
End Sub

 

[Andrius B.]

OK, that I have understand myself, at what place the desired row number is
to be specified

Thank You both for the code.

 

[Rick Rothstein \(MVP - VB\)]

For row #13, for example:

OK, that I have understand myself, at what place the desired row
number is to be specified

I rearranged the relevant parts of the posted messages for clarity. Notice
that Gary''s Student started by saying "for row #13" and then in his posted
code uses 13 as the first argument for the Cells Range object? Just replace
the 13 with the row you want to check. If it helps you any, you can turn
this code into a function to make your calling it simpler...

Function LastColumn(RowNumber As Long) As Long
LastColumn = Cells(RowNumber, Columns.Count).End(xlToLeft).Column
End Function

This way, assuming the function is in scope, you can use it directly in your
code. For example

LastColumnInRow5 = LastColumn(5)

or

MsgBox "There are " & LastColumn(5) & " columns being used in row 5."

Rick

 

[Gary''s Student]

It could be a built-in constant or you could as the User:

Sub findit()
Dim n As Long
n = Application.InputBox(Prompt:="enter ruw number")
MsgBox (Cells(n, Columns.Count).End(xlToLeft).Column)
End Sub