selecting a range from a list

[Y.Kim]

i am importing a list and i only need part of the list for
my report. my list contains data with columns id, acct,
security, coupon, maturity, principal. i only need to work
with data that has an id of 1-5. a lot of the data in the
list doesn't have an id. so after i sort the list i only
need to select the data with id 1-5 then copy that
selection and paste into sheet 2.

can't figure out a vba solution to this. can anyone one
give guidance.

tks.

 

[Tom Ogilvy]

just apply an autofilter to the data

Data=>filter=>Autofilter

in the id column, select 1-5 from the dropdown, select all the data and
copy,
then go to the destination and do Edit=>Paste

Turn on the macro recorder while you do this and you will have tha basic
code you need. You don't need to sort. Only the visible cells will be
copied and pasted by default.

 

[Y.Kim]

some items have id 1 or 2 or 3 or 4 or 5..id "1-5" doesn't
exit. and my import list changes in size daily..if it was
the same size then the macro recorder would be fine, but
my list size changes.

i sort by id so i can get items with either id 1, 2, 3, 4,
5 first on top of the list. then i usually delete the
remaining rows without id

seeking a way to automate this procedure.

thank you,

 

[Dave Peterson]

When you do your filter (once manually), you'll see that you can use Custom.
Inside that option, you can specify "greater than or equal to" 1 AND "less than
or equal to" 5.

Record that macro when you do it and you'll see the code.

 

[Tom Ogilvy]

If you select a single cell in the range, then apply the autofilter, Excel
will apply it to the "currentregion", so changing size doesn't affect it.