Macro problems

[Father John]

Hi I am trying to record a simple macro where if I press alt+d the location
moves 7 "page down" key strokes - about 100 rows.
However when I record the macro it resorts back to the line number that my
macro originally ended up at 7 keystrokes from the top., even if I am 1500
lines down.
How can I resolve this?

With thanks
Stephen

(My apologies if this is duplicated as I cannot see the original post after
12 hours)

 

[Dave O]

When you record a macro, Excel picks up all the details: where is the
cell pointer located, etc, and assumes you want to replicate that each
time. Most likely your macro has a row in it that says something like
Range("A1").Select, which is where you were when you recorded.

To fix it, save your working file and work from a backup copy so you
don't lose data. Open the file; on the menu click >Tools >Macro

Macros and highlight the macro you recorded; press Edit. The Visual

Basic editor opens, and shows you the instructions you recorded. Near
the top there will likely be a Range("A1").Select command. You can
delete it, or enter an apostrophe before that command, which signals
the compiler to disregard that row.

Good luck with it!
Dave O
Eschew Obfuscation

 

[Pierre]

Hi.

You would need to define your starting point as a variable, not a specific
cell.

 

[Dave Peterson]

Sometimes, it's better to describe what you want to do.

For instance, if I want to select the cell after the last used cell in column A,
I'd use something like:

Dim NextCell as range
dim Wks as worksheet

with wks
set nextcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

nextcell.select

I like to start at the bottom of the worksheet and look upwards. Some look
downward from the cell. But if there are empty cells in the range, that may not
do what I want.