AutoFilter 0's & Non-Blanks

**ExcelQuestion** · 09-20-2008, 03:02 AM

Hello Experts,
I need your help please. The following the code from the macro recorder. Is there a better way to execute this task in proper VBA code?

How do you add an [If...Then] critieria when 0 or 0.00 are not found, then continue to search for the next criteria, Non-Blanks?

I have 2 more questions within this code:

Please Login or Register  to view this content.

Thanks in advance,
Ricky

**royUK** · 09-20-2008, 04:20 AM

You don't need to use Select. Try this, I haven't tested it

Please Login or Register  to view this content.

**broro183** · 09-20-2008, 04:32 AM

For Q1, try changing Roy's:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

hth
Rob

**ExcelQuestion** · 09-21-2008, 03:56 AM

Originally Posted by royUK

You don't need to use Select. Try this, I haven't tested it

Please Login or Register  to view this content.

Thank-you RoyUK and broro183,
I have ran your codes. It errors at the 3rd line from the bottom.

Please Login or Register  to view this content.

It also errors because the entire Row 1 will be deleted. I'd like to retain the title row. The problem has something to do with this line. When there's no match to the filter criteria, it would still delete the visible row...with is row 1.

Please Login or Register  to view this content.

I need more help please.

Thanks in advance again,
Ricky

**ExcelQuestion** · 09-21-2008, 05:50 AM

After some trial and testing, this code seems to work okay. How do I add another filter criteria deleting all rows with "0.00" under the "ExportTotal" name? By the way, what does Option Explicit do? The code seems to work with or without it.

Please Login or Register  to view this content.

Thanks again,
Ricky

**VBA Noob** · 09-21-2008, 05:56 AM

Please Login or Register  to view this content.

is used to force you to declare variables which is good coding practice. See link for more

http://msdn.microsoft.com/en-us/libr...4f(VS.80).aspx

See link under More than two criteria
http://www.rondebruin.nl/delete.htm#AutoFilter

VBA Noob

**royUK** · 09-21-2008, 07:27 AM

Try this slightly amended code

Please Login or Register  to view this content.

**ExcelQuestion** · 09-22-2008, 01:53 AM

Hello RoyUK,
I've tried your amended code. It did not filter the 0 nor the Blanks, and it still deleted the top Title row. You've provided a platform for me though.

With RoyUK, broro183, and VBANoob's guidance, this is the code that does work.

Please Login or Register  to view this content.

I appreciate everyone's valuable input. Thanks a lot.

Ricky

**royUK** · 09-22-2008, 03:47 AM

Your on error line needs moving

Please Login or Register  to view this content.

**ExcelQuestion** · 09-25-2008, 02:56 PM

Originally Posted by royUK

Your on error line needs moving

Please Login or Register  to view this content.

Thanks RoyUK,
I've also updated the next 2 lines. It seems to work okay, but is this right?

Please Login or Register  to view this content.

Ricky

**royUK** · 09-26-2008, 02:30 AM

Try this

Please Login or Register  to view this content.

**ExcelQuestion** · 10-04-2008, 10:35 PM

Thanks again RoyUK.

How do you change the last line within the existing code...

Please Login or Register  to view this content.

...so that in case if I were to insert columns then the range selection would remain unchanged? It's the 65536,18 (column J) part that's written in the code and I need to make it become flexible so that the column number will change as I insert or delete columns.

Thanks,
Ricky

**broro183** · 10-07-2008, 03:19 AM

hi Ricky,

Once a post is "solved" I think it is best to ask further questions in a new post (I stand to be corrected though...

).

Hopefully this helps:
Create a name for the cell in Excel by selecting the cell (I'm assuming it's a header cell with "For Export" typed into it), then press [alt + i + n + d], [Add] & [Ok] (this should create the Name "For_Export"). This name can then be used in the below code which should do what you're after...

Please Login or Register  to view this content.

hth
Rob

**royUK** · 10-07-2008, 04:01 AM

My first reply told you that you don't need to select ranges in most cases. It also uses UsedRange which will always be the range used on the sheet, no matter how many columns or rows are added. An alternative is to use CurrentRegion.