Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 21
Default 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"
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options Yuvraj Excel Discussion (Misc queries) 0 June 29th 09 11:20 AM
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Yuvraj Excel Discussion (Misc queries) 0 June 26th 09 06:01 PM
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 [email protected] Excel Worksheet Functions 1 August 22nd 08 02:04 AM
How to create/run "cell A equals Cell B put Cell C info in Cell D abmb161 Excel Discussion (Misc queries) 5 January 26th 06 07:36 PM
data validation to restrict input in cell based on value of cell above that cell NC Excel Programming 2 January 25th 05 08:11 AM


All times are GMT +1. The time now is 11:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"