VBA - Last Cell In A Row
 

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


--
ajocius
------------------------------------------------------------------------
ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=391590

VBA - Last Cell In A Row
 

On Sat, 30 Jul 2005 20:28:03 -0500, ajocius
wrote in
microsoft.public.excel.programming:

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

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

VBA - Last Cell In A Row
 

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


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=391590

VBA - Last Cell In A Row
 

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


ajocius wrote:

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

--
ajocius
------------------------------------------------------------------------
ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=391590

--

Dave Peterson

VBA - Last Cell In A Row
 

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

ajocius wrote:

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

--
ajocius
------------------------------------------------------------------------
ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=391590

--

Dave Peterson