Hiding rows based on cell content

[brutus]

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]

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.

 

[brutus]

Hi Brutus.


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


Consider using the built in autofilter feature.

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

 

[Norman Jones]

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

 

[Norman Jones]

Hi Brutus,

The initial code section:

ActiveSheet.DisplayPageBreaks = False

With Application
.ScreenUpdating = True

Should read:

ActiveSheet.DisplayPageBreaks = False

With Application
.ScreenUpdating = False

 

[brutus]

Hi Brutus,

The initial code section:


Should read:

ActiveSheet.DisplayPageBreaks = False

With Application
.ScreenUpdating = False

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