Select Cell and Over 1

[tweek312]

I need to select a range of cells that changes often at a specific point in my code but I dont know how.

Sub create_er()

    Selection.AutoFilter Field:=23, Criteria1:="<>"
    Range("W16").Select
    Range(Selection, Selection.End(xlDown)).Select

After this part I need to select 20 rows to the left. The selected data will then be copied into a specific area on another worksheet. The data could be anywhere from 1 to 30 rows and spans 21 columns.

Anyting helps! Thankz

tweek

 

[PHV]

Have you tried to play with Offset and Resize ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kpetursson]

Range(Cells(2, 1), Cells(5, 8)).Select

the first cells(2,1) is the upper left, and the second cells(5,8) the is lower right.
cells(rowindex,colindex) so row B=2 and F=6

Hope this is what you were looking for.

Kevin Petursson

 

[tweek312]

The vertical selection cannot be absolute which is why I use "Selection.End(xlDown)).Select

 

[tweek312]

I have tried offset but I dont know how to keep the current selection and offset to another. I am not familiar with resize.

 

[TonyJollans]

Hi tweek312,

Your code only makes sense if you know W16 is not blank.

Given that, all you need to do is change:

[blue]Range(Selection, Selection.End(xlDown)).Select[/blue]

to:

[blue]Range(Selection, Selection.End(xlDown))[red].Offset(, -20).Resize(, 21)[/red].Select[/blue]

The Offset shifts the start 20 columns to the left and the Resize makes it 21 columns wide

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[tweek312]

Thanks A Lot!

Works great!

 

[SkipVought]

I've been watching this thread and decided to post a followon comment, cuz, there's more than one way to skin a cat.

Extensive use of the Activate and Select methods significantly slows down your code and is sometimes ambiguous. Check out...

How Can I Make My Code Run Faster? faq707-4105

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[tweek312]

Very Interesting, I will apply this in the future as my code in this case runs fast enough.

 

[ChrisBurch]

Thanks Skip, that deserved a star.

Chris

IT would be the perfect job......if it didn't have users!!!