+ Reply to Thread
Results 1 to 6 of 6

Hiding rows based on cell content

Hybrid View

  1. #1
    brutus
    Guest

    Hiding rows based on cell content

    I need to know if there is any way (other then VB code which I already use)
    to hide a row of a spreadsheet depending on the contents of a cell. I have
    a spreadsheet that calculates commissions on data imported fromn Quick
    Books. SOme employees do not get commissioned on all the items imported.
    If a particular line item is non-commissionable I want that line to be
    hidden before printing. I am presently using VB code to hide the rows but
    it takes a couple of minutes to act on a 1000 row spreadsheet.

    Dave



  2. #2
    Norman Jones
    Guest

    Re: Hiding rows based on cell content

    Hi Brutus.

    > it takes a couple of minutes to act on a 1000 row spreadsheet.


    On the face of it, that would seem excessively long. Post the code that you
    are using.

    >I need to know if there is any way (other then VB code which I already use)


    Consider using the built in autofilter feature.


    ---
    Regards,
    Norman



    "brutus" <brutus@thebrute.com> wrote in message
    news:fMdcf.1517$Xo.367@newsread2.news.pas.earthlink.net...
    >I need to know if there is any way (other then VB code which I already use)
    >to hide a row of a spreadsheet depending on the contents of a cell. I have
    >a spreadsheet that calculates commissions on data imported fromn Quick
    >Books. SOme employees do not get commissioned on all the items imported.
    >If a particular line item is non-commissionable I want that line to be
    >hidden before printing. I am presently using VB code to hide the rows but
    >it takes a couple of minutes to act on a 1000 row spreadsheet.
    >
    > Dave
    >




  3. #3
    brutus
    Guest

    Re: Hiding rows based on cell content


    "Norman Jones" <normanjones@whereforartthou.com> wrote in message
    news:unYSjwN5FHA.692@TK2MSFTNGP11.phx.gbl...
    > Hi Brutus.
    >
    >> it takes a couple of minutes to act on a 1000 row spreadsheet.

    >
    > On the face of it, that would seem excessively long. Post the code that
    > you are using.
    >
    >>I need to know if there is any way (other then VB code which I already
    >>use)

    >
    > Consider using the built in autofilter feature.
    >
    >
    > ---
    > Regards,
    > Norman


    Sub HURows()
    BeginRow = 1
    EndRow = 1200
    ChkCol = 16

    For RowCnt = BeginRow To EndRow
    If Cells(RowCnt, ChkCol).Value = 4 Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    ElseIf Cells(RowCnt, ChkCol).Value = 2 Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    ElseIf Cells(RowCnt, ChkCol).Value = 6 Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    ElseIf Cells(RowCnt, ChkCol).Value = 5 Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    Else
    Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    End If
    Next RowCnt
    End Sub

    Dave



  4. #4
    Norman Jones
    Guest

    Re: Hiding rows based on cell content

    Hi Brutus,

    Try this adaptation:

    '===========>>
    Sub HURows()
    Dim BeginRow As Long
    Dim EndRow As Long
    Dim ChkCol As Long
    Dim calcmode As Long

    BeginRow = 1
    EndRow = 1200
    ChkCol = 16

    ActiveSheet.DisplayPageBreaks = False

    With Application
    .ScreenUpdating = True
    calcmode = .Calculation
    .Calculation = xlManual
    End With
    For RowCnt = BeginRow To EndRow
    If Cells(RowCnt, ChkCol).Value = 4 Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    ElseIf Cells(RowCnt, ChkCol).Value = 2 Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    ElseIf Cells(RowCnt, ChkCol).Value = 6 Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    ElseIf Cells(RowCnt, ChkCol).Value = 5 Then
    Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    Else
    Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    End If
    Next RowCnt

    With Application
    .ScreenUpdating = True
    .Calculation = calcmode
    End With

    End Sub
    '<<===========


    ---
    Regards,
    Norman



    "brutus" <brutus@thebrute.com> wrote in message
    news:52Scf.209$104.110@newsread1.news.pas.earthlink.net...
    >
    > "Norman Jones" <normanjones@whereforartthou.com> wrote in message
    > news:unYSjwN5FHA.692@TK2MSFTNGP11.phx.gbl...
    >> Hi Brutus.
    >>
    >>> it takes a couple of minutes to act on a 1000 row spreadsheet.

    >>
    >> On the face of it, that would seem excessively long. Post the code that
    >> you are using.
    >>
    >>>I need to know if there is any way (other then VB code which I already
    >>>use)

    >>
    >> Consider using the built in autofilter feature.
    >>
    >>
    >> ---
    >> Regards,
    >> Norman

    >
    > Sub HURows()
    > BeginRow = 1
    > EndRow = 1200
    > ChkCol = 16
    >
    > For RowCnt = BeginRow To EndRow
    > If Cells(RowCnt, ChkCol).Value = 4 Then
    > Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    > ElseIf Cells(RowCnt, ChkCol).Value = 2 Then
    > Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    > ElseIf Cells(RowCnt, ChkCol).Value = 6 Then
    > Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    > ElseIf Cells(RowCnt, ChkCol).Value = 5 Then
    > Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    > Else
    > Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    > End If
    > Next RowCnt
    > End Sub
    >
    > Dave
    >




  5. #5
    Norman Jones
    Guest

    Re: Hiding rows based on cell content

    Hi Brutus,

    The initial code section:

    > ActiveSheet.DisplayPageBreaks = False
    >
    > With Application
    > .ScreenUpdating = True


    Should read:

    ActiveSheet.DisplayPageBreaks = False

    With Application
    .ScreenUpdating = False


    ---
    Regards,
    Norman



    "Norman Jones" <normanjones@whereforartthou.com> wrote in message
    news:%23A%23$9Mm5FHA.2676@TK2MSFTNGP15.phx.gbl...
    > Hi Brutus,
    >
    > Try this adaptation:
    >
    > '===========>>
    > Sub HURows()
    > Dim BeginRow As Long
    > Dim EndRow As Long
    > Dim ChkCol As Long
    > Dim calcmode As Long
    >
    > BeginRow = 1
    > EndRow = 1200
    > ChkCol = 16
    >
    > ActiveSheet.DisplayPageBreaks = False
    >
    > With Application
    > .ScreenUpdating = True
    > calcmode = .Calculation
    > .Calculation = xlManual
    > End With
    > For RowCnt = BeginRow To EndRow
    > If Cells(RowCnt, ChkCol).Value = 4 Then
    > Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    > ElseIf Cells(RowCnt, ChkCol).Value = 2 Then
    > Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    > ElseIf Cells(RowCnt, ChkCol).Value = 6 Then
    > Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    > ElseIf Cells(RowCnt, ChkCol).Value = 5 Then
    > Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    > Else
    > Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    > End If
    > Next RowCnt
    >
    > With Application
    > .ScreenUpdating = True
    > .Calculation = calcmode
    > End With
    >
    > End Sub
    > '<<===========
    >
    >
    > ---
    > Regards,
    > Norman




  6. #6
    brutus
    Guest

    Re: Hiding rows based on cell content


    "Norman Jones" <normanjones@whereforartthou.com> wrote in message
    news:u%23CzJTm5FHA.1420@TK2MSFTNGP09.phx.gbl...
    > Hi Brutus,
    >
    > The initial code section:
    >
    >> ActiveSheet.DisplayPageBreaks = False
    >>
    >> With Application
    >> .ScreenUpdating = True

    >
    > Should read:
    >
    > ActiveSheet.DisplayPageBreaks = False
    >
    > With Application
    > .ScreenUpdating = False
    >
    >
    > ---
    > Regards,
    > Norman
    >
    >
    >
    > "Norman Jones" <normanjones@whereforartthou.com> wrote in message
    > news:%23A%23$9Mm5FHA.2676@TK2MSFTNGP15.phx.gbl...
    >> Hi Brutus,
    >>
    >> Try this adaptation:
    >>
    >> '===========>>
    >> Sub HURows()
    >> Dim BeginRow As Long
    >> Dim EndRow As Long
    >> Dim ChkCol As Long
    >> Dim calcmode As Long
    >>
    >> BeginRow = 1
    >> EndRow = 1200
    >> ChkCol = 16
    >>
    >> ActiveSheet.DisplayPageBreaks = False
    >>
    >> With Application
    >> .ScreenUpdating = True
    >> calcmode = .Calculation
    >> .Calculation = xlManual
    >> End With
    >> For RowCnt = BeginRow To EndRow
    >> If Cells(RowCnt, ChkCol).Value = 4 Then
    >> Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    >> ElseIf Cells(RowCnt, ChkCol).Value = 2 Then
    >> Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    >> ElseIf Cells(RowCnt, ChkCol).Value = 6 Then
    >> Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    >> ElseIf Cells(RowCnt, ChkCol).Value = 5 Then
    >> Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    >> Else
    >> Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    >> End If
    >> Next RowCnt
    >>
    >> With Application
    >> .ScreenUpdating = True
    >> .Calculation = calcmode
    >> End With
    >>
    >> End Sub
    >> '<<===========
    >>
    >>
    >> ---
    >> Regards,
    >> Norman


    Norman - Worked fine! I had to tell it to start at row 16 (the first 15
    didn't contain data that needed to be hidden). Yours completes the task in
    about a second. Excellent.

    Thaks for your help!

    Dave



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1