Macro to hide empty rows

**nomis6565** · 11-21-2012, 09:23 PM

Hi
I have a macro on a sheet to hide all empty rows based on the values of cells A55 to A215. The value comes from a droplist that is filled by the users. If the value “1” appears, the row is visible, if nothing appears, the row should hide automatically.

The macro is working if I change the value manually in column A ("1" or empty) but the fact that the value comes from a Vlookup formula linked to a drop list (H29:H36) caused some problems... So according to the selection in the drop list, the value changes from empty to "1" and this must decide if the row is hide or not. The problem is when I change the value from the droplist, nothing happen. Do you think you could find out why?

Thanks,

Please Login or Register  to view this content.

Dropbox link:
https://dl.dropbox.com/u/93601544/TE...WS%20EXTR.xlsm

**AlphaFrog** · 11-21-2012, 09:52 PM

The Worksheet_Change event procedure is triggered when the user (not a formula) makes a change to the worksheet. The Target within the procedure will be the cell that the user changed and not the cells that changed from the formulas.

So this line...
If Not Intersect(Target, Range("A55:A255")) Is Nothing Then
...is testing if the Target is one of the formula cells and Target is the cell that the user changed.

The fix is to change A55:A255 to the cells that the user changes e.g.
If Not Intersect(Target, Range("H29:H36")) Is Nothing Then

**nomis6565** · 11-23-2012, 05:45 PM

Superbe!, it works perfectly. Would it be possible add a command in the macro to make it work in 2 ways. I mean that for now, the macro works only in one way, if I change a value from "1" to "0" it will hide the row, but if i make another change that switch the "0" to "1" it should unhide the row containing a 1". I'm sure you can make it work!

Thanks
Simon

**AlphaFrog** · 11-23-2012, 11:49 PM

Originally Posted by nomis6565

Superbe!, it works perfectly. Would it be possible add a command in the macro to make it work in 2 ways. I mean that for now, the macro works only in one way, if I change a value from "1" to "0" it will hide the row, but if i make another change that switch the "0" to "1" it should unhide the row containing a 1". I'm sure you can make it work!

Thanks
Simon

Try this...

Please Login or Register  to view this content.

**nomis6565** · 11-24-2012, 04:03 PM

I knew you would do it! It's just perfect. Thanks so much, It allowed me to terminate the most complex project I worked on Excel with VBA.

Ragards,
Simon