| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hiding rows based on a cell? | Excel Worksheet Functions | |||
| Hiding rows based value of cell within row | Excel Discussion (Misc queries) | |||
| Copy rows based on cell content | Excel Discussion (Misc queries) | |||
| hiding rows based on cell contents | Excel Discussion (Misc queries) | |||
| Hiding rows based on cell content | Excel Programming | |||
Linear Mode
