ExcelBanter - Hiding rows based on cell content

ExcelBanter

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Hiding rows based on cell content (https://www.excelbanter.com/excel-programming/345103-hiding-rows-based-cell-content.html)

brutus

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

Norman Jones

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" wrote in message
k.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

brutus

Hiding rows based on cell content

"Norman Jones" wrote in message
...
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

Norman Jones

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" wrote in message
k.net...

"Norman Jones" wrote in message
...
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

Norman Jones

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" wrote in message
...
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

Hiding rows based on cell content

"Norman Jones" wrote in message
...
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" wrote in message
...
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

All times are GMT +1. The time now is 04:20 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com