Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: hide rows

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    New Member
    Join Date
    Mar 2002
    Location
    London
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    London
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, both bit's of code together:

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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •