VBA - Last Cell In A Row

[ajocius]

Group,
How can I find the last cell in a row? I have this spreadsheet
that has approximately 1500 rows. Each row has a different last
column. The columns in a row can vary between 5 to 52. I want the
value to be saved in a Variable. Your help can make this weekend go
smoother.

Working to be a REAL VBA programmer one day.........


Thank you in advance.

Tony

 

[Michael Bednarek]

How can I find the last cell in a row? I have this spreadsheet
that has approximately 1500 rows. Each row has a different last
column. The columns in a row can vary between 5 to 52. I want the
value to be saved in a Variable. Your help can make this weekend go
smoother.

There are probably a few different methods to find the last column in a
row. Here's one:

Dim lngNRows As Long
Dim lngNCols As Long
Dim i As Long

lngNRows = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

For i = 1 To lngNRows
lngNCols = Cells(i, 256).End(xlToLeft).Column
Debug.Print "Row " & i & " has " & lngNCols & " column(s)."
Next i

This will not distinguish between rows where column "A" is empty or not
- the answer is "1".

 

[dominicb]

God morning ajocius

As an alternative try this, which will find the last row (where you
state the row number - in this example, 4) and NextRow is the variable
name that the value is passed to:

NextRow = Application.WorksheetFunction.CountA(Range("4:4"))

HTH

DominicB

 

[Dave Peterson]

Do you really mean just the last row for any particular row?

dim LastCol as long
dim iRow as long

irow = 38 'whatever row you want.
with worksheets("sheet1")
lastcol = .cells(iRow,.columns.count).end(xltoleft).column
msgbox .cells(irow,lastcol).value & .cells(irow,lastcol).address
end with

 

[Dave Peterson]

Another way that I took from Debra Dalgleish's site:
http://www.contextures.com/xlfaqApp.html#Unused

Option Explicit
Sub testme()

Dim myLastRow As Long
Dim myLastCol As Long

With ActiveSheet
myLastRow = 0
myLastCol = 0
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0
End With

MsgBox myLastRow & vbLf & myLastCol

End Sub