Hide rows in Excel

[MDA]

Hi all,

I have an Excel sheet with three columns:
Col1, Col2, Col3(Variance)

I would like to create a function that hides the row if the variance is over x percent. I will have one NAMED cell to define the variance breaking point. I am not sure where to start with this.. Any ideas?

Note, the total number of rows will constantly change.

Appreciate any advice!
Regards,
MDA

 

[xlbo]

This'll have to be a sub - a worksheet function or UDF CANNOT change the environment - they can only produce an answer.
If you don't want to go with code, conditional formatting should do the trick
Post back and let me know whether you want code or not... Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[andrew299]

Very crude method-if mit does what I think you want


Sub hide()
Range("d1&quot.Select ' D1 is your variance
xx = ActiveCell.Value

For x = 1 To 3 '1-3 = columns 1-3
Cells(1, x).Select

Do Until ActiveCell.Value = ""

If ActiveCell.Value > xx Then
Selection.EntireRow.Hidden = True
End If
ActiveCell.Offset(1, 0).Select


Loop



Andrew299

 

[MDA]

Code is what I am looking for... I came up with:
-----------------
Sub HideRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 8 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).EntireRow.Hidden = True

Next r
End Sub
------------------
But this deletes if the row is blank.. Now I need to throw in the variance check to compare the variance column(D) to my variance variable in a named cell (variance)...

I am going to look at what Andrew299 suggested.. Any other ideas??

Thanks,

MDA

 

[Nikita6003]

Say your variance breaking point is on Sheet1, in a named range called BreakingPoint. You could use:

  Dim l_dBreakingPoint As Double

  l_dBreakingPoint = ThisWorkBook.Sheets(1).Range("VBreakingPoint")
  For r = LastRow To 8 Step -
    If ThisWorkBook.Sheets(1).Cells(r,4) > l_dBreakingPoint Then
      Rows(r).EntireRow.Hidden = True
    End If
Next r

Hope this is what you mean ..

Cheers
Nikki

 

[Loomah]

A slightly different approach would be to use filters. Have your criteria in the named cell (in this case "vari&quot and you're away. I admit this ain't pretty but it works!

Range("A1").AutoFilter Field:=3, Criteria1:=">=" & Range("vari")

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[MDA]

Thanks for all the input...

I went with Nikita's solution...

Thanks again.

MDA