Selecting cells until first filled cell

[kl88]

Heya !

I was hoping someone could help me here,
im having a bit of trouble getting a macro to run the way i would like it to.
Im trying to get the macro to select a range , from for example row Z10 ( always different so activecell ) to row before the last filled cell , for example row Z5.
But all i can get the macro to do is select the last cell, not the range until.

    Range("Z1").End(xlDown).Copy
    Range("B1").End(xlDown).Select
    Range("Z" & ActiveCell.Row).Select

This is what i got so far, hope someone can help !
Thanks in advance !

 

[Andrew Poulsom]

Like this?

Range(Range("Z1"), Range("Z1").End(xlDown)).Copy

 

[kl88]

Hey thanks for anwsering,

But the code doesnt really do what i ment.

Range(Range("Z1"), Range("Z1").End(xlDown)).Copy

The code as it is now, selects from Z1 down. It also selects the cells with values in them.

I need the code to go from the activecell its at ( for example Z10 ) (activecell is empty) . To the last empty cell in the column, selecting all the cells in between. for example Z5 if Z4 has a value in it.

 

[Andrew Poulsom]

Please post some samples of your data and tell us what you want to copy for each one. Your example is confusing because Z5 is above Z10.

 

[kl88]

Ok well, i cant provide a sample but i will try to be more clearer.
row/column Z
1 Requester 1
2 Requester 2
3 Requester 3
4 Requester 4
5
6
7
8
9
10 Activecell !

So as i try to explain, my activecell is Z10,
now i want to select all the cells from Z10 to Z5 (which are empty) so i need to go upwards. But the cells can be anything , its not set to a certain cell. I hope this makes it clearer !

 

[Andrew Poulsom]

For that example:

Range(ActiveCell, ActiveCell.End(xlUp).Offset(1)).Copy

 

[kl88]

Awesome !! Thanks ^^ this was exactly what i was looking for !!