Hide Rows
 

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

Hide Rows
 

Assume the dropdown is in B10 of the sheet.
Right click on the sheet tab and select view code. Put in code like this:

Change B11:B1010 to the range where you want to check for zero values.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell as Range
if Target.count 1 then exit sub
If Target.Address(0,0) = "B10" then
range("B11:B1010").EntireRow.Hidden = False
for each cell in range("B11:B1010")
if cell.Value = 0 then
cell.EntireRow.Hidden = True
end if
Next
End Sub
--
Regards,
Tom Ogilvy


"Shail" wrote:

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