• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel VBA - hide rows with value 0

Johnny3000

New Member
Hi everyone,

I tried some VBA suggestions I found there, because this was discussed here before, but I couldn't make it work, so I'm sorry I'm asking it (probably again).

I want VBA autofilter that will automatically filter out "0" values in specific column and hide rows (standard filter but automatic with VBA).

I'm attaching the file with this post. I want in the sheet "Table" to run VBA on the column "D" that will hide automatically "0" value rows.
The VBA should run whenever there is any change in column "C" (this is where the original data will be inserted) in sheet "Insert".

Thank a lot to everyone willing to help!
 

Attachments

  • 28122019 VBA autofilter question.xlsm
    59.1 KB · Views: 4
Hi,

thank you for the reply!

Does the VBA work for you when the sheet "Insert" is empty and you paste (Ctrl + V) here some data (eg. the data that are in the attached file)?
It doesn't seem to auto apply the filter for me.

Thank you very much in advance
 
Johnny3000
Your: The VBA should run whenever there is any change in column "C" (this is where the original data will be inserted) in sheet "Insert".
With pasting
- If You're pasting to 'C'-column then 'Yes'
- if pasting eg to columns 'BC' then 'No' (if Your used Target.column is different than 'C*)
... anyway, for me - inserting always include to press <ENTER> to 'end' inserting - until that - it's still in 'editing-mode'.

If do any change in C-column 'Yes' (with <ENTER>) no matter which row - is cell empty or not!
... but but
... that 'Filtering' could do many many times! ( after every inserting in C-column )
... ... eg if You'll insert 99 times something in C-column, there could happen 99 times filtering.


Many times, You could see the result of Filtering by Your eyes after You have selected Table-sheet for view
.... or do You have a master plan to use Filtered result somewhere BEFORE selection?
or
... would this solve that Table-sheet would Filter only when select Table ... as with this version?
 

Attachments

  • 28122019 VBA autofilter question.xlsm
    64.4 KB · Views: 5
Superb, this VBA seems to solve my case. Thank you very much!
Can I ask you another question? I don't know if this needs to be VBA or just some excel formula...
I would like to automatically refresh and update on sheet "Table" cells A636 and further.
The source data should be all unique numbers from sheet "Data", column "H".

Have a nice day
 
Johnny3000
Table's A636 ...
Hmm? If You meant this ... then that could do this way ...
 

Attachments

  • 28122019 VBA autofilter question.xlsm
    69.2 KB · Views: 4
I'm getting Run-time error '438' when selecting sheet "Table".

with this line highlighted:

.SortFields.Add2 Key:=Range("O2:O" & o_max), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 
Johnny3000
Your sent file opened here as normally as possible.
Quit Your Excel ( Quit really means quit! )
and
test this version after that.
 

Attachments

  • 28122019 VBA autofilter question.xlsm
    69.5 KB · Views: 5
Back
Top