MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

"Generic" Selection

  Asked By: Asksuresh    Date: Oct 29    Category: MS Office    Views: 1535

Can someone tell me how to code this?

1. I'm in the first row of the last column,
2. I insert a column
3. I insert a formula into the first cell of the new column. (My
options are set so that I don't move the active cell when I hit
3. With the active cell at the top of this new empty column, I do a
shift-ctrl-end to select a range that includes the first cell in the
new column, to the last cell in the spreadsheet.
--here is where the recorded macro breaks--
4. Still holding the shift key down, I left-arrow one column. So now
I have only the new empty column selected. This is the next to last
column in the spreadsheet.
5. Fill down the formula from the first row to the last row.

The recorded macro captures this code for the selection process:

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

It seems that when I did the shift-left-arrow, it selected the
absolute range, "T1:T3107". So now if the spreadsheet I want to work
on has 3108 rows, my macro doesn't know that and effectively "breaks".

I've tried using the VBA help file, but can't seem to find out how to
make this code more generic. I'm thinking I need to do something
like a "ActiveCell.SpecialCells(xlLastCell)-1". I found the offset
command, but I don't seem to be able to use that here (or can't
figure out how!).



1 Answer Found

Answer #1    Answered By: Hu Chalthoum     Answered On: Oct 29

In VBA help, look up OFFSET, often (though not in this case) used in
combination with RESIZE.

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