Hide rows

[shail]

Hi,

I have a dropdown validation list. When I select an item from this
list, the rows below will be populated using the HLOOKUP function. The
HLOOKUP function brings the values from the other sheet. The records
will be one to one thousand. And I have the formula upto 1000 rows.
When item has just one value in it the other rows will have the value
zero.

I need to hide those rows which have the zero values. I could have use
the Autofilter, but I need it when the item is selected from the
dropdown list. So this could be done using VBA code.

Please help


Shail

 

[Don Guillett]

Not only are you posting the same thing over and over again you are posting
in several groups. Bad!!

 

[PCLIVE]

To get the code for doing this, click tools-macro-record macro.

Basically, place your autofilter manually while recording the action. Then
you can use that code.

Here's an example of some code.
You could use something like this.

Columns("I:I").Select 'this places the autofilter on column I
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>" & Range("B1").Value,
Operator:=xlAnd
' line above assumes that the validation dropdown is in B1

Right-click on the worksheet tab that you will be using this on. Select
'View code'. In left dropdown, select 'Worksheet'. In right dropdown,
select 'Change'. Then paste code. That should get you started.

HTH,
Paul

 

[shail]

Sorry Don,

I didn't mean to post is again and again but there was some problem in
the group and I couldn't be able to see my message appearing in the
group. And I posted it repeatedly.

Shail