Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2000 - Macro assistance please (Excel 2000 SR2)

    I have an existing macro in my personal.xls that i want to run.
    Code:

    Sub drughide()
    Dim rcell As Range
    Dim rng As Range
    Set rng = Range(Range("d4"), Range("d2000")
    For Each rcell In rng
    If IsNumeric(rcell.Value) Then
    If rcell.Value = "" Then _
    rcell.Rows.Hidden = True
    End If
    Next

    End Sub

    Now when i run the macro it stops at row 703

    Now my data is about 900 rows.

    can anyone suggest a change to my code.
    I just want to hide any rows where the entry in column d is blank. data starts at row4. I have freezr panes set on rows 1 to 3

    I have another macro to unhide which works fine.

    thank you

    carl cross

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 - Macro assistance please (Excel 2000 SR2)

    The line which reads
    <pre> Set rng = Range(Range("d4"), Range("d2000")
    </pre>


    is missing a ")" at the end.

    The following works for me:

    <pre>Sub drughide()
    Dim rcell As Range
    Dim rng As Range
    Set rng = Range(Range("d4"), Range("d2000"))
    For Each rcell In rng
    If IsNumeric(rcell.Value) Then
    If rcell.Value = "" Then
    rcell.Rows.Hidden = True
    End If
    End If
    Next rcell
    End Sub
    </pre>


    Also, is it possible that the cells after row 703 are not really empty? Do they contain a space, or a formula that returns a blank or null string?
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel 2000 - Macro assistance please (Excel 2000 SR2)

    If you are just trying to hide the rows that are blank in col d, this should be much faster Especially if there are lots of blanks:

    <pre>Sub drughide2()
    Dim rCell As Range
    Dim rng As Range
    Set rng = Nothing
    On Error Resume Next
    Set rng = Range(Range("d4"), Range("d2000")).SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not rng Is Nothing Then _
    rng.Rows.Hidden = True
    End Sub</pre>


    Steve

Posting Permissions

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