Last cell location

[sa02000]

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

 

[Tom Ogilvy]

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

 

[sa02000]

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

Ja

 

[Tom Ogilvy]

-- 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 Re: 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]

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