Active cell position for macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am running a macro to add rows or remove rows assigned to a form button.
The problem is if the active cell is moved from the last blank row the marco
won't run.

Is it possible to have the macro alway return the cursor to the required
active cell before it runs?
 
May have been better if you had posed some of yout code but try something
like:

ActiveCell.End(xlUp).Offset(1, 0).Select

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Without seeing your code it is hard to say why your macro won't run if
activecell is not in last blank row.

This will move the cursor to last blank cell in column A

Sub foo()
Dim rcell As Range
Set rcell = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
Application.Goto Reference:=rcell
MsgBox "Activecell is" & rcell.Address
End Sub


Gord Dibben MS Excel MVP
 
ActiveCell.Offset(-1, 1).Select

NOTE: not always necessary to select a cell in order to do something with it.

Many "selects" will slow down your code execution.


Gord Dibben MS Excel MVP
 
What about a couple simple cursor move commands?

That is... if such things even exist.
 
That is what Offset does in conjunction with Select

You mean moves like the old Lotus bigup, bigdown, smallright etc.?

Excel does not have that method of moving the cursor around.

Can you show an example of what you want the cursor to do relative to its
current position?


Gord
 
That is what Offset does in conjunction with Select

You mean moves like the old Lotus bigup, bigdown, smallright etc.?

Excel does not have that method of moving the cursor around.

Can you show an example of what you want the cursor to do relative to its
current position?
Click to expand...

When I am recording a macro, it will write out move commands like when
I go to the last cell in a column, and then out to the last cell in that
row, so it does do similar things to "end", "home", "Ctrl-End",
"Shift-End", etc.

But I can see where this can introduce errors, and why it was best to
not manipulate things in this way. It is kind of like a remote session
where you do not know where the cursor is at.

Or that old dungeon game where you play blind, aided only by text
queues.

I have had issues with my recorded macros where subsequent new data
causes it to fail as the 'table' is no longer the same 'array size'.
So I had to find better, more correct methods of selecting all the data
in a worksheet. Recorded macros can have sections where shorter, other
code segments would work better. But then, I guess a macro recorder
would have to record such moves.

It would seem that one could poll the current cell location and use
math to determine the newly desired location, and then use a move command
of some sort to place the cursor at that cell location, and it not cause
big slowdowns.
 
You want to be able to work with dynamic ranges?

Basically you use the method of going to bottom then working up to find last
used cell in a column.

Sub lastcell()
'find last cell in activecolumn
ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
End Sub

Or going to right then coming back to last used cell in a row.

Sub findlastcolumn()
'find last column in activerow
ActiveSheet.Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Select
End Sub

Or use the CurrentRegion method.

Sub select_to_cell()
'select to bottom right cell in current region or selected range
Dim rng As Range
Set rng = ActiveCell.CurrentRegion
rng.Select
rng(rng.Count).Activate
End Sub

Sub find_cell_alone()
'select just bottom right cell of current region or selection
Dim rng As Range
Set rng = ActiveCell.CurrentRegion
rng(rng.Count).Select
End Sub

For many more examples see Ron de Bruin's site.

http://www.rondebruin.nl/last.htm


Gord
 
Back
Top