conditional deletion

ptahl · Mar 31st, 2002, 07:44 AM

If there are 4 cells of data per row:date, time, price and volume and we want to delete all rows except those rows which have a volume cell value of >= 1000, how is the best achieved?
Thanks in advance.
ptahl

Chris Davison · Mar 31st, 2002, 08:08 AM

not sure if it's *best*, as some VBA code will do it quicker, but autofiltering on that column should do the trick, leaving you the rows to delete manually

dk · Mar 31st, 2002, 08:28 AM

To add to Chris's reply. If you autofilter using a custom criteria of <1000. Then highlight all the data and press F5 and then Special. Choose the Visible Cells Only option button. Then click Edit, Delete and choose Entire Row. That will delete all of the rows under 1000 in one go. Remove the autofilter and you'll be left with the data you want.

Regards,
Dan.

Aladin Akyurek · Mar 31st, 2002, 09:00 AM

Quote:

On 2002-03-31 06:44, ptahl wrote:
If there are 4 cells of data per row:date, time, price and volume and we want to delete all rows except those rows which have a volume cell value of >= 1000, how is the best achieved?
Thanks in advance.
ptahl

Insert 3 to 4 rows before your data.

Supposing that the data area consists of columns A thru D,

in A1 enter: Volume
in A2 enter: >=1000

Activate A4.
Activate Data|Filter|Advanced Filter.
Check Copy to another location.
Assure that the List range covers your data area.
Enter as Criteria range

$A$1:$A$2

Enter as value of Copy to, e.g.,

$F$1

Click OK.

The area starting at $F$1 is what you're looking for. You can ditch everything else if you so desire.

[ This Message was edited by: Aladin Akyurek on 2002-03-31 08:01 ]

Jack in the UK · Apr 2nd, 2002, 04:34 PM

Hi guys

check

http://www.mrexcel.com/board/viewtop...3863&forum=2&4

Thiss my VBA i developed.. woth a look edit to delet not hide i guess wil do it...

Hope some option....

Mar 31st, 2002, 08:08 AM	#2
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	not sure if it's best, as some VBA code will do it quicker, but autofiltering on that column should do the trick, leaving you the rows to delete manually __________________ :: Pharma Z - Family drugstore ::

Apr 2nd, 2002, 04:34 PM	#5
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	Hi guys check http://www.mrexcel.com/board/viewtop...3863&forum=2&4 Thiss my VBA i developed.. woth a look edit to delet not hide i guess wil do it... Hope some option.... __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 31st, 2002, 07:44 AM	#1
ptahl New Member Join Date: Mar 2002 Posts: 8	If there are 4 cells of data per row:date, time, price and volume and we want to delete all rows except those rows which have a volume cell value of >= 1000, how is the best achieved? Thanks in advance. ptahl

Mar 31st, 2002, 08:28 AM	#3
dk MrExcel MVP Join Date: Feb 2002 Location: Sydney, Australia Posts: 2,908	To add to Chris's reply. If you autofilter using a custom criteria of <1000. Then highlight all the data and press F5 and then Special. Choose the Visible Cells Only option button. Then click Edit, Delete and choose Entire Row. That will delete all of the rows under 1000 in one go. Remove the autofilter and you'll be left with the data you want. Regards, Dan.