Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

keypress shortcuts

  Asked By: Loretta    Date: Oct 31    Category: MS Office    Views: 835
  

Newbie here stumped on a supposedly easy problem. I am trying to
record a macro that would select the cell to the left of the last
row, last column with data. After that highlight from that point
upto the first cell in that column.

The shortcut keys sequence I press when doing this manually are:

1. <ctrl><end> 'gets me to the last row, last column with data'
2. <left> 'selects cell to the left of above'
3. <ctrl><shift><up> 'highlights from #2 to the first cell'

To do the first line, macro recorder spits correctly the code:
ActiveCell.SpecialCells(xlLastCell).Select

The next two lines is where Excel does not give correct code whether
I press the absolute or relative coordinate button.

Any help on the proper code for the next two lines is greatly
appreciated.

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Abasi Massri     Answered On: Oct 31

You've found where the recorder  really doesn't help  much, in
programmatically locating and selecting variable locations of cells.
The recorder provides the absolute  selection statements, which aren't
very useful in the long run.

The first thing to do is review Help on "Range" and Cells, two very
useful keywords to know and love.

Now to discuss the specifics of your questions...

> Newbie here stumped on a supposedly easy  problem. I am trying to
> record a macro  that would select  the cell  to the left  of the last
> row, last column  with data. After that highlight  from that point
> upto the first cell in that column.
>
> The shortcut  keys sequence  I press  when doing this manually  are:
>
> 1. <ctrl><end> 'gets me to the last row, last column with data'

You've got this part already with the ActiveCell statement.

Now you need to grab and save those coordinates to make use of them in
the next part...

First, add these at the start of your subroutine to keep the
coordinates in:
Dim iRow as Integer
Dim iCol as Integer

Now, after your line  from (1.) you need to capture the coordinates of
that cell with:
ActiveCell.SpecialCells(xlLastCell).Select
iRow = ActiveCell.Row
iCol = ActiveCell.Column

With the information you now have with the statements above, you can
form a Range statement using the Cells keyword that completes the next
two parts ...

> 2. <left> 'selects cell to the left of above'

Selecting the cell to the LEFT of the current cell means using the
SAME row, and one less than the current column, gives the statement:
Cells(iRow,iCol - 1)

> 3. <ctrl><shift><up> 'highlights from #2 to the first cell'

The location of the top of the column is in row  # 1, or whatever row
is really the top of your data. If you have a row of labels as a
header, the row is 2, etc.

So that location in the Cell format is:
Cells(1,iCol - 1)

Now, putting that together into a range, you need the Range statement
selecting from either the bottom up to the top or the top down. I
prefer top to bottom so the Range statement becomes:

Range(Cells(1,iCol - 1),Cells(iRow,iCol - 1)).Select

Now we have that whole column selected, so your sub looks like this:

Sub SelectColumn()
Dim iRow as Integer
Dim iCol as Integer
ActiveCell.SpecialCells(xlLastCell).Select
iRow = ActiveCell.Row
iCol = ActiveCell.Column
Range(Cells(1,iCol - 1),Cells(iRow,iCol - 1)).Select
End Sub

 
Answer #2    Answered By: Aylin Kaya     Answered On: Oct 31

I am eager to try it out
tonite and will let you know of the results.

 
Answer #3    Answered By: Rhys Evans     Answered On: Oct 31

The code  works flawlessly. Appreciate the help! Thank you!

 
Answer #4    Answered By: Mildred Bailey     Answered On: Oct 31

Double check 'where' is the 'Active' column:
If it is respect to the LastCell, the code  returns the 2nd to the last column
in the data  set.
If the intent is upon an active cell  in column  'D', & you are looking to
select column 'C' in the code, we need to make a slight adjustment to grab the
active column 'before' moving to the 'LastCell':

Sub SelectColumn( )
Dim iRow as Integer
Dim iCol as Integer

iCol = ActiveCell.Column

ActiveCell.SpecialC ells(xlLastCell) .Select
iRow = ActiveCell.Row
Range(Cells( 1,iCol - 1),Cells(iRow, iCol - 1)).Select
End Sub

 
Answer #5    Answered By: Lee Butler     Answered On: Oct 31

You haven't commented on the code  you're getting, and you haven't said which
version of Excel you're using.

I've just tried it (Excel 2003) and do see a difference between absolute  and
relative:

- the xlLastCell move is the same in both modes - specifically
ActiveCell.SpecialCells(xlLastCell).Select;

- the left  move becomes an absolute location selection in absolute mode, but
is an Offset call to move the appropriate number of columns in relative
mode - specifically ActiveCell.Offset(0, -1).Range("A1").Select - which
moves one cell  to the left of wherever you are;

- the ctrl-shift-up is the same in both modes - specifically
Selection.End(xlUp).Select.

So, this key sequence  appears to be recorded correctly  in relative  mode, and
should cope with extra rows as they are created.

One point though ... "<ctrl><shift><up> 'highlights from #2 to the first
cell'". This description is not technically accurate and could possibly be
leading to trouble. Ctrl-Up will stop on a blank/non-blank boundary. It'll
only take you to the first cell if all the cells in the area it's traversing
are empty or all the cells are non-empty.

Anyway. Record it again in relative mode and post the code it generates.
Also indicate what it's doing wrong.

 
Answer #6    Answered By: Jennifer Davis     Answered On: Oct 31

I am using Excel97. I will try
Phillip's method first. If that fails, yours will be next. The code
Excel2003 spitted out gave me enough clues on what the excel  constants
should be for my particular keypresses.

 
Answer #7    Answered By: Beaudi Smith     Answered On: Oct 31

Thanks for your comments about recording in "relative mode"...

> this key sequence  appears to be recorded correctly  in relative  mode

I don't believe I've ever used that mode before, and was pleasantly
surprised with the results!

Headline: "Old dog just learned new trick!"

 
Didn't find what you were looking for? Find more on keypress shortcuts Or get search suggestion and latest updates.




Tagged: