Last cell location
 

Lets say I have 100 rows and 5 columns (upto E column) of data in
spreadsheet. What will be the best way to get the location of last cel
in the spread-sheet (in this case E100) so that I can use row and colum
variables. I would like to use those when I want to write a formula i
one cell and copy that along the column or do a selection etc.

Thanks,
Ja

--
sa0200
-----------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=47256

Last cell location
 

set rng = cells(rows.count,"E").End(xlup)

--
Regads,
Tom Ogilvy

"sa02000" wrote in
message ...

Lets say I have 100 rows and 5 columns (upto E column) of data in a
spreadsheet. What will be the best way to get the location of last cell
in the spread-sheet (in this case E100) so that I can use row and column
variables. I would like to use those when I want to write a formula in
one cell and copy that along the column or do a selection etc.

Thanks,
Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile:
http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=472569

Last cell location
 

is there a way to not hardcode column...
Also, can i use these row and column variables in a vlookup formula?

Ja

--
sa0200
-----------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=47256

Last cell location
 

-- yes, you can use the results returned. Here are some other ways:

If you want to find the "RealLastCell with data:"


In a thread subject Change What Excel Believes Is The Last Cell
In Microsoft.Public.Excel.Programming on October 15, 1999


This was posted by John Green
"The following code will find the last row and last column that contain data
on the active worksheet:"


Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
End Sub


[ the cell selected is at the intersection of the last row and last column
and may be empty, so you can use each result independently]


This is a version posted by David Chazin with some additional error checking


Sub LastCell_Select()
'David Chazin 05-Mar-1999
'Selects the cell at the bottom-right corner of the used area
' of the currently selected worksheet.
Range(LastCell_Get()).Select
End Sub


Function LastCell_Get() As String


'David Chazin -- September 1999.
'Returns the Address (e.g. "$AW$235") of the cell at the
' bottom-right corner of the used area of the currently
' selected worksheet. Returns "" if the worksheet is empty.
' Thanks to Dave Braden for the idea of trapping for an
' empty worksheet. (This is not the way he would implement
' it, but the idea itself is his).


On Error GoTo LastCell_Get_ErrorHandler


If Range("A1").SpecialCells(xlLastCell).Value < "" Then
LastCell_Get = Range("A1").SpecialCells(xlLastCell).Address()
Else
LastCell_Get = _
Cells(Cells.Find("*", ActiveCell.SpecialCells(xlLastCell), , , _
xlByRows, xlPrevious).Row, _
Cells.Find("*", ActiveCell.SpecialCells(xlLastCell), , , _
xlByColumns, xlPrevious).Column).Address()
End If
Exit Function
LastCell_Get_ErrorHandler:


If Err.Number = 91 Then
'If the worksheet is empty....
LastCell_Get = ""
Else
Call MsgBox("Error #" & Err.Number & " was generated by " & _
Err.Source & ": " & Err.Description, vbOKOnly + vbExclamation, _
"LastCell_Get()", Err.HelpFile, Err.HelpContext)
End If


End Function


Regards,
Tom Ogilvy

"sa02000" wrote in
message ...

is there a way to not hardcode column...
Also, can i use these row and column variables in a vlookup formula?

Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile:
http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=472569

Last cell location
 

How do I use a variable in a name of a sheet or a file or something.
For example, every month I create a new file and do similar step
except the name of month on sheets and file names. So, if I have shee
named Aug_download last month, this month I want to name this sheet a
Sep_download. So, I was thinking I can create a user input of mont
name and then concatenate that to the name of sheet. But how do I ge
around actually doing it?

Ja

--
sa0200
-----------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=47256