what is the Excel macro command equivalent to "end up"

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

Guest

I am trying to get a macro to go to the equivalent of the keystrokes end up
no matter where end up takes me. However, the record keystrokes macro
actually goes to the specific cell that end up took me when I recorded the
macro no matter where end up now takes me.

What I want the macro to do is as follows.

end, up, shift, right ctrl C, goto A25, ctrl V

In other words go up to the first cell with data, copy that cell and the one
to the right of it, and paste the copied data to row 25 columns A & B.
 
Hi Jim,

Is this what you ara after?

Sub test()
With ActiveSheet
.Range("A25:B25")=.Cells(65536, "A").End(xlUp).Resize(, 2)
End With
End Sub


Regards,
KL
 
Thanks for the reply KL,
Not quite. First the macro runs a routine which finds the largest number in
a list (auto filter to 10 command). This works OK. Next I want to go to the
cell where that number is located and copy the contents of that cell and the
one to the right of it and then paste what I copied to another location.
The autofilter command hides all the rows in the list except the row that
contains the largest number. So, depending on the list, various rows get
hidden.
When I tried to make a "record keystrokes" macro do this, I used the end up
keys to go to the cell that contained the largest number. Then I used shift
right to highlight the two cells. Then I used ctrl V to past the copied
information elsewhere.
However, the macro didn't do the equivalent of end up. It recorded the
actual cell that end up took me to. So, when I run the macro, it goes to
that specific cell every time even though that cell may not now contain the
largest value in the list.
 
Maybe this would help:

Dim rng As Range, tvalue

tvalue = Worksheetfunction.Max(Range("A:A")

Set rng = Cells.Find(tvalue)
If Not rng Is Nothing Then
MsgBox rng.Address
Else
MsgBox "Not Found"
End If

WkBk2.Sheets("InputSheet").Cells(rw,col) =
WkBk1.Sheets("MyData").Range(rng.address)

It is not always required to select anything.
The above finds the address of the cells and you can use this to copy to
another location.
 
Thanks for the help Steve,

Works fine until it gets to the last two lines of code. I assume I should
substitute something for some of the things in these two lines, but am
stumped. The message does always show the correct cell.

Also, is there not a macro command equivalent to the end and up keys? or for
the shift right keys? or for the ctrl C command? or for the ctrl V command?
 
These have to be on a single line. Note the line contiuation symbol _

WkBk2.Sheets("InputSheet").Cells(rw,col) = _
WkBk1.Sheets("MyData").Range(rng.address)

WkBk1 & WkBk2 are variable representations of 2 different workbooks
InputSheet & MyData are generic names for worksheets.
You need to change these to match your own situation.

The easiest way to capture the end up, down, sideways, etc commands is to
open a new workbook and just play with the macro recorder. Than edit the
code that is generated.

Recording is a great way to learn basic coding.

Be aware that my code is designed in such a way that there is no selecting
or activating.
When you can do that - the code runs faster.

-
steveB

Remove "AYN" from email to respond
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top