A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

VBA - Last Cell In A Row



 
 
Thread Tools Display Modes
  #1  
Old July 31st 05, 02:28 AM posted to microsoft.public.excel.programming
ajocius[_14_]
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

Ads
  #2  
Old July 31st 05, 05:31 AM posted to microsoft.public.excel.programming
Michael Bednarek[_8_]
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  
Old July 31st 05, 07:58 AM posted to microsoft.public.excel.programming
dominicb[_79_]
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  
Old July 31st 05, 01:28 PM posted to microsoft.public.excel.programming
Dave Peterson
external usenet poster
 
Posts: 35,220
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  
Old July 31st 05, 01:32 PM posted to microsoft.public.excel.programming
Dave Peterson
external usenet poster
 
Posts: 35,220
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 06:38 PM.


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