XLdown then up 1 !!!

[melewie]

Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am having trouble with a bit of code. Select the empty cells between to cells.<o></o>
<o></o>
Really struggling to do this. Offset moves the whole selection range! I’m guessing it will be something like this<o></o>

[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Range(Selection, Selection.End(xlDown).Row - 1).Select<o:p></o:p>[/FONT][/COLOR]

<o></o>
<o></o>
Thanks in advance<o></o>
<o></o>
Lewie<o></o>

 

[xenou]

Is selection in the first empty cell or the last cell with a value (before the first empty cell)?

depending on the above,

Either:

Range(Selection, Selection.End(xlDown).Offset(-1, 0))

Or:

Range(Selection.Offset(1,0), Selection.End(xlDown).Offset(-1, 0))

-- note that we didn't use .Row at all - using the reference(s) to the cell(s) is handier here.

 

[melewie]

Cheers xenou,

but offset moves the entire selection up 1. All I want to do is reduce the selection by 1??

Any ideas

 

[Misca]

So maybe you're talking about resizing your selection?

Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

 

[melewie]

yes thats what im trying to do. Cheers

This code takes one off the top of the selection I need to remove 1 from the bottom???

Any ideas

 

[Misca]

Resizing the range always happens from the bottom up (and from the right to the left) so I had to add the "Offset(1,0)" part to move the selection down one before taking out the bottom row.

All you need to do is leave out the "Offset(1,0)" part:

Selection.Resize(Selection.Rows.Count - 1).Select

 

[melewie]

Works perfectly!!!

Thank you