hide rows

Joanna_gr

Board Regular
Joined
Feb 16, 2002
Messages
149
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0
No problem, I would always go for a non-VBA solution first anyway, saves possibly re-inventing the wheel :).
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top