MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA Guys needed - finding the last cell + 1


Posted by Phil on July 27, 2001 7:30 AM

Does any1 know how i can fin the the last value plus 1 in the current collumn.... i think it is somethingh along these lines....

Cells(Selection.End(xlDown) + 1).Select,1)

but, it doesn't seem to work....!!!!



Posted by Aladin Akyurek on July 27, 2001 7:43 AM

Phil,

You might be able to use the following code Rodney Powell:

QUOTE

Function LastCell(ws As Worksheet) As Range
Dim LastRow&, LastCol%

' Error-handling is here in case there is not any
' data in the worksheet

On Error Resume Next

With ws

' Find the last real row

LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

End With

' Finally, initialize a Range object variable for
' the last populated row.

Set LastCell = ws.Cells(LastRow&, LastCol%)

End Function

Using this Function:

The LastCell function shown here would not be used in a worksheet, but would be called from another VBA procedure. Implementing it is as simple as the following example:

Example:

Sub Demo()

MsgBox LastCell(Sheet1).Row

End Sub


UNQUOTE

Aladin

Posted by Dax on July 27, 2001 9:02 AM

Use:
If MsgBox("Are you sure you want to run the macro?", vbYesNo) = vbNo Then
Exit Sub
Else
End If


Barrie

Posted by Joe Was on July 27, 2001 10:08 AM

Re: VBA Code to select a cell

Sub myLast()
'
' By Joseph S. Was
'Find the last cell with data in column "B."
Range("B65536").End(xlUp).Select

'Select a cell away from the last cell.
'Note: Offset(Row, column) [+number is that
'number of cells Right or Down from the active
'cell and -number is that number of cells Left
'or Up from the current active cell location!]

'This takes the active cell "B1" and moves the
'selection to cell "A2."
ActiveCell.Offset(1, -1).Select

'This takes the active cell "A2" and moves the
'selection to "B2."
ActiveCell.Offset(0, 1).Select
End Sub

I was not certain what you meant by "plus 1"; up, down, left, right or something else?

So, I hope this helps you? JSW


Posted by Malc on July 28, 2001 3:23 AM

Record a macro of you selecting the cell at the top of the column and pressing the end button and then then the down Arrow.

You'll get the bit of code in your question
Then add
Cells(ActiveCell.Row+1,1).select