Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hide Blank Rows (97)

    I have a price list spreadsheet (attached).

    I need a macro to hide rows where there are no quantities (column I is blank).

    The trickly bit is, that for rows which are not hidden, (column I has a value), I also want to keep its heading (the bold item in column [img]/forums/images/smilies/cool.gif[/img].

    Is this possible? If so, how? I've made an attempt, and attached it to the button labelled "Remove blank rows" but it is flawed and there must be a better way.

    Thanks in advance!

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

    Re: Hide Blank Rows (97)

    Your code does not fit your description: it checks columns M and D instead of I and B. Moreover, the named range Endeth is not valid in the workbook you attached., so I more or less rewrote the macro.

    Sub DelBlankRows()
    ' Macro created by M E Adams & Associates Pty Ltd meadams@ozemail.com.au, c January 2005,

    Dim myrange As Range, blnHideHeader As Boolean, strPassword As String, lngMaxRow As Long, lngRow As Long
    strPassword = Range("pword").Value

    On Error GoTo NoDel

    'Hide Blank rows
    If MsgBox("Do you want to HIDE blank rows in this worksheet?", vbYesNo, "HIDE blank rows") = vbNo Then
    Exit Sub
    End If

    'Turn off screen updating
    Application.StatusBar = "Removing blank rows - Please Wait."
    Application.ScreenUpdating = False

    'Make sure sheet is unprotected
    ActiveSheet.Unprotect Password:=strPassword

    blnHideHeader = True
    'set up the range
    lngMaxRow = Range("A65536").End(xlUp).Row
    For lngRow = lngMaxRow To 15 Step -1
    If Cells(lngRow, 1) = "" Then
    ' heading
    Rows(lngRow).Hidden = blnHideHeader
    blnHideHeader = True
    ElseIf Cells(lngRow, 9) = "" Then
    Rows(lngRow).Hidden = True
    Else
    blnHideHeader = False
    End If
    Next lngRow

    NoDel:
    Application.ScreenUpdating = True
    ActiveSheet.Protect Password:=strPassword
    Application.StatusBar = False
    End Sub

    This version also reprotects the worksheet and clears the status bar.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Blank Rows (97)

    Hans,
    Thank you very much. I never thought of working from the bottom up - this is one of the great things about this Lounge, you get new ideas of how to approach issues.

    Very grateful. Thanks again.

Posting Permissions

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