Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 2010 Macro/VB to hide 0.00 values from a table 1

Status
Not open for further replies.

pendle666

Technical User
Jan 30, 2003
295
GB
Hello

I've got a spreadsheet which calculates sales along with a little table which summarises the totals.

Here is an example:


[pre]
H I J

Name Sales Warranties

48 John Brown £999.99 £229.00
49 Michael Green £- £-
50 Dave Black £- £-
51 Fiona White £1919.99 £-

[/pre]

The entries with figures in them will be copied/pasted to another sheet. So what I was doing was filtering the Sales column to take out those with £- and doing it that way.

But I wanted to click a button to filter it and then reset it again.

I used the macro recorder and came up with this:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1 _
:=Array("£1,919.99", "£999.99", "="), Operator:=xlFilterValues


But this only works when those exact figures are there. If I do another report, and the figures/people are different then it doesn't work.

Can someone help me out and show me what I need to be looking at?

Thank you




thank you for helping

____________
Pendle
 
hi,

FILTER:
Code:
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:="<>0"

CLEAR:
Code:
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2


Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Thank you!

thank you for helping

____________
Pendle
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top