hide rows

[Joanna_gr]

Hallo!

To explain better my previous posting:
In a col. A i have data as follows:
opt
real
pess
opt
real
pess

Is there a way that whenever i type "P" in a cell (let'say C1=P) automatically hide all rows in col. A that have "opt" or "real".
Please help me with that!!!!

Regards
Joanna

[gbarclay2001]

If you write a macro that contains the following you could use that if there's a suitable way to call the code.

Rows("9:15").Select

Can you run a macro by entering specific text into a cell? Well, probably. I don't know how but I'm sure someone here does.


Selection.RowHeight = 0

[gbarclay2001]

Sorry, both bit's of code together:

Rows("9:15").Select
Selection.RowHeight = 0

[Joanna_gr]

Thanks for that, but this is not what i want. The condition is that if cell A3="P" then show only rows on col. B that says "pess" and "real" and hide all rows with "opt"
Maybe an advanced filter or something but i don't know how to do it...

On 2002-04-12 04:37, gbarclay2001 wrote:
Sorry, both bit's of code together:

Rows("9:15").Select
Selection.RowHeight = 0

[AJ]

Hi,

A macro solution could run something like this...
(This goes in the Sheet code, not the module)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$1" And Range("C1").Value = "P" Then
Lastrow = Range("A65536").End(xlUp).Row

Range("A1").Select

Do While ActiveCell.Row < Lastrow + 1

If ActiveCell.Value = "opt" Or ActiveCell.Value = "real" Then
Rows(ActiveCell.Row).Select
Selection.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If

Loop

End If

End Sub

I don't know whether this is the best way of doing this, but it seems to work.

Rgds
AJ

[Mudface]

If your data is in the form of a list, you could use autofilter as follows (paste this into the worksheet code section): -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim myString As String

myString = Left(Target.Text, 1)
UsedRange.AutoFilter Field:=1, Criteria1:=myString & "*"

End Sub

Might be worth a try, apply autofilter first before entering the code.

[Joanna_gr]

I used advance filter and it worked. Thank you very much.

To be honest i'm not sure if i can make the code work. I'm not very used to it. if u have some time u may explain a bit more to me. Otherwise it's ok. Thanks anyway. You help me a lot as always

[Mudface]

No problem, I would always go for a non-VBA solution first anyway, saves possibly re-inventing the wheel .