Last Used Cell in a row??

  • Thread starter Thread starter Andrius B.
  • Start date Start date
A

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?
 
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
 
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
 
For row #13, for example:

Sub findit()
MsgBox (Cells(13, Columns.Count).End(xlToLeft).Column)
End Sub
 
OK, that I have understand myself, at what place the desired row number is
to be specified :)

Thank You both for the code.
 
For row #13, for example:
OK, that I have understand myself, at what place the desired row
number is to be specified :)
Click to expand...

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