Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing | Excel Discussion (Misc queries) | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
data validation to restrict input in cell based on value of cell above that cell | Excel Programming |