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

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
>

"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

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
>

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 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