Remove all numbers >=3 but leave T and L .

Hello all ,

I am needing a fast working macro that can remove in col G all numbers >=3 and leave cells blank , but leave behind all T and L .

Thanks .

Try

Please Login or Register  to view this content.

Thanks for reply , there will not be any numbers <=2 .
Other words there are no 1,s or 2,s there at all , these are referred to as T and L which are to be left only .

Thanks .

there are no 1,s or 2,s

So here a remake

Please Login or Register  to view this content.

Fluff13 , yours deletes everything in col G .

PCI your first one went error yellow on line
Range(Cells(1, "G"), Cells(Rows.Count, "G").End(3)).SpecialCells(xlCellTypeVisible).ClearContents

And your 2nd one did nothing except remove all my headings

Also im testing on a 490,000 row sheet .

Thanks .

A remake with no test with so many rows ...!

Please Login or Register  to view this content.

That one just took the filter off the headings . Thanks .

Can you just do a firsttest with the file you attached.
BTW your file did not have any 1's and 2's ...!

Same player shot again
Another one perhaps it takes time ...!

Please Login or Register  to view this content.

Originally Posted by Rick Rothstein

You cannot use SpecialCells on the OP's data... he has 490,000 rows of data and is using XL2007 (8000+ limit for SpecialCells if I remember correctly).

*** CORRECTION ***

It has been too many years since I had to deal with this and I had remembered incorrectly... the limit for Excel prior to 2010 is not 8000+ cells, it is 8000+ (actually, 8192) non-contiguous ranges (Areas) no matter how many cells are in each Area. So I am guessing the OP has blank rows (lots of them) within his 490,000 rows of data. Here is a mini-blog article I wrote about this (with a method to get around the limit) several years ago...

http://www.excelfox.com/forum/showth...Cells-Function

Try this:
I assumed the values of column G are only numbers or "T" or "L".
The code will sort the data by col G in descending order then remove all rows below the last "L".

Please Login or Register  to view this content.

Edit:
If you only want to clear the content of col G, use this one:

Please Login or Register  to view this content.

Originally Posted by I need excel help

im testing on a 490,000 row sheet .

See if this works faster...

Please Login or Register  to view this content.

Try this code.It is fastest.

Please Login or Register  to view this content.

Thx again Rick:
for the information and to take care until the last detail

Please Login or Register  to view this content.

WBD

thanks to all