How to name the last used cell?

Lowell In the south

Board Regular
Joined
Sep 26, 2002
Messages
55
I know this is simple but I am taking those baby steps.

I am tring to make my macro return you to the last cell you were working in no matter where you are in the worksheet when it runs. I recived a suggestion to have the Macro name that cell prior to running and then select that cell after it runs. Will that work more than once? How do i code somting like that?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sounds like what you want is

Range("A1").SpecialCells(xlCellTypeLastCell).Select

That will put you at the last cell each time it is run (even if you change where the last cell is).

F.T.
 
Upvote 0
It appear that bit of code takes me to the last cell in the last column and the last row of the spread sheet. Make sense? Thanks for your help!
 
Upvote 0
Hi Lowell,

This will name a cell or range of cells when its/their contents change.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Name = "lastCellChanged"
End Sub

You can select it/them with:
Range("lastCellChanged").select
 
Upvote 0
Howdy Lowell, yes, this is a good way to do it, it'll overwrite. If you have you the following following code in the appropriate worksheet module:<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Name = "MyCell"
End Sub</pre>

Right-click the sheet tab, select view code and paste in the code.

Then in a normal module you had code like the following:<pre>
Sub GoBck()
On Error Resume Next 'for the halibut
Application.Goto "MyCell"
End Sub</pre>

Running GoBck will return you to the last edited cell if a cell on the correct worksheet. Or click f5 and select MyCell.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-10-02 18:05
 
Upvote 0
Thanks for the suggestions!!!! Appears to be only one problem my macro is called by a change event. The macro copies and pastes a range in to several other ranges. I seems that this bit of code sees the last selected range as the "target" No matter where I put it In the worksheet change even or at the top of the macro. Make sense? Thanks much for your help!!!!!!!!!!
 
Upvote 0
What about this:

Sub test()

'save position current cell
currCell = ActiveCell.Address
currSheet = ActiveSheet.Index

' do your stuff
'copy
'paste
'......

'return to saved position
Sheets(currSheet).Activate
Range(currCell).Activate

End Sub
 
Upvote 0
THanks!!! I used that bit of code before I called two diffrent macros that cut and pasted with in the same work sheet and it works great!!!!
ac = ActiveSheet.Name
Dim rng As Range
Set rng = Cells(ActiveCell.Row, ActiveCell.Column)

Dim C As Range
' *** Change sheet name to suit ***
For Each C In Sheets("Window Inputs").Range("AG6:AG63")
If UCase(C.Value) = "NO" Then
Range("AH:AH").EntireColumn.Hidden = False
Range("AJ:AO").EntireColumn.Hidden = False
Exit Sub
End If
Next C
'*** Call your other procedure here ***
Call Only_one_Panning_size_per_window
Range("AH:AH").EntireColumn.Hidden = True
Range("AJ:AO").EntireColumn.Hidden = True


' ====================== only one trim size per window====================


Dim D As Range
' *** Change sheet name to suit ***
For Each D In Sheets("Window Inputs").Range("AP6:AP63")
If UCase(D.Value) = "NO" Then
Range("AQ:AQ").EntireColumn.Hidden = False
Range("AS:AX").EntireColumn.Hidden = False
Exit Sub
End If
Next D
'*** Call your other procedure here ***
Call Only_one_trim_Size_per_window
Range("AQ:AQ").EntireColumn.Hidden = True
Range("AS:AX").EntireColumn.Hidden = True

Sheets(ac).Select
rng.Select

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top