Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Hide Rows (Excel 2000/2003)

    Hi

    I have adapted the code posted by Hans in response to another user, It works, but it seems to hide one row at time, is there a way to speed it up a bit.

    Public Sub Hide()
    Dim i As Integer
    For i = 10 To 169
    If Range("G" & i) <> "M" Then
    Rows(i).Hidden = True
    End If
    Next i
    End Sub

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hide Rows (Excel 2000/2003)

    One way would be to suspend screen updating during the macro, by inserting a line

    Application.ScreenUpdating = False

    at the beginning, and

    Application.ScreenUpdating = False

    just before the End Sub.

    If the data are in the form of a table with headings, you could use AutoFilter or Advanced Filter; you wouldn't need a macro (but it would be possible to apply the filter in a macro).

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hide Rows (Excel 2000/2003)

    Hi Hans

    Although the data is in the form of a table there are too many blanks to use Autofilter, I am not sure about advanced Filter, as this is not my workbook I am not inclined to re-design it.

    Having said that your reply seems to work OK and should be acceptable.

    Many Thanks for your prompt reply.

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hide Rows (Excel 2000/2003)

    I have attached a version that uses AutoFilter. The button acts as a toggle.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hide Rows (Excel 2000/2003)

    Hi Hans

    This seems to be a better way of doing this task, but If the user removes the autofiter then runs the macro again autofilter does not work, unless you know different. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hide Rows (Excel 2000/2003)

    To make it work whether AutoFilter is present or not, change the code to

    Public Sub Hide()
    If ActiveSheet.AutoFilterMode Then
    If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
    Exit Sub
    End If
    End If
    ActiveSheet.Range("C10:F169").AutoFilter Field:=4, Criteria1:="=M*"
    End Sub

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hide Rows (Excel 2000/2003)

    Hi Hans

    That is perfect.

    Thank you Very Much.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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