EXCEL Hide rows or columns

[CTruax]

How can a row be hidden based on
-a cell value in that row or
-the format-pattern color of a cell in the row

I would like to see all rows, but when I go to print, certain rows would be hidden

 

[onlyadrafter]

Hello,

The way I would do is to do a BEFORE_PRINT event.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
For MY_COLUMNS = 1 To Range("IV5").End(xlToLeft).Column
If Range("A1").Offset(4, MY_COLUMNS - 1).Value = 1 Then
Columns(MY_COLUMNS).Hidden = True
End If
Next MY_COLUMNS
End Sub

This code needs to go into the THIS WORKBOOK COde window.
It will hide columns if the cells in row 5 contain 1.

Change cell refs and criteria as needed.



----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[onlyadrafter]

Hello again,

forgot about the color (3= red)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
For MY_COLUMNS = 1 To Range("IV5").End(xlToLeft).Column
If Range("A1").Offset(4, MY_COLUMNS - 1).Value = 1 Or Range("A1").Offset(4, MY_COLUMNS - 1).Interior.ColorIndex = 3 Then
Columns(MY_COLUMNS).Hidden = True
End If
Next MY_COLUMNS
End Sub

What do you want to do after the print, unhide the columns?



----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[FacilitiesCAD]

Onlyadrafter, your code is easy to follow and helpfull but could you explain what "IV5" and x1toleft refer to in the context of the code?

For MY_COLUMNS = 1 To Range("IV5").End(xlToLeft).Column

Tim

 

[onlyadrafter]

Hello,

What I trying to find is the last used column in row 5, so I start at column IV, which is the last column available on the spreadsheet, and come left (xltoleft) this will find the first cell from the right which is not blank.

To replicate this manually, enter data in G1,goto column IV (row 1), press the CTRL key and the left arrow together, he cursor will go to G1.

This is done to cut down on time, as checking every column for data/colour is time consuming. More so when checking rows, as there are 65536.

Explanation adequate?



----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[CTruax]

'this works but
'How do I get the maximum# of rows in a worksheet?
'I'm at 20 now
'and
'From a CommandButton1 Sub, the .Hidden property ERRORS

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target = "SHOWN" Then Range("A1").Value = "HIDDEN" Else If Target = "HIDDEN" Then Range("A1").Value = "SHOWN" Else Exit Sub

Dim MY_ROWS As Integer, MY_COLUMNS As Integer, COLOR_FLAG As Integer
COLOR_FLAG = 26
MY_ROWS = 20
For MY_ROWS = 2 To MY_ROWS
If Range("A1").Offset(MY_ROWS - 1, 0).Interior.ColorIndex = COLOR_FLAG Then
Rows(MY_ROWS).Hidden = (Range("A1").Value = "HIDDEN") 'BOOLEAN
End If
Next MY_ROWS
End Sub

 

[onlyadrafter]

Hello,

Do you mean

MY_ROWS=range("A65536").end(xlup).row



----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[CTruax]

'ToDo still Do you know a work-around these limitations
'1. Data must exist in the maximum Row/Column to be checked
' Is there a property that gives the maximum in use?
'2. The Sub runs with every cell change. Reduce cpu overhead.
' A CommandButton1 Sub causes the .Hidden property to error
'3. This works if the cell is A1
' How to get the row and column number of the target cell so that
' if >Row1 then sub does rows only
' if >Col1 then sub does columns only

'This subroutine hides/shows rows/columns which have specified color.index
'Put ShOwN or HiDdEn into column 1, row 1, or both (cell A1)
'Data must exist in the last cell or column (to determine max index)
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target = "ShOwN" Then Range("A1").Value = "HiDdEn" Else If Target = "HiDdEn" Then Range("A1").Value = "ShOwN" Else Exit Sub
Dim MY_INDEX As Integer, COLOR_FLAG As Integer
COLOR_FLAG = Target.Interior.ColorIndex 'hide/show only these color lines
For MY_INDEX = 2 To Range("A65536").End(xlUp).Row 'check rows
If Range("A1").Offset(MY_INDEX - 1, 0).Interior.ColorIndex = COLOR_FLAG Then Rows(MY_INDEX).Hidden = (Range("A1").Value = "HiDdEn") 'BOOLEAN
Next MY_INDEX
For MY_INDEX = 2 To Range("IV1").End(xlToLeft).Column 'check columns
If Range("A1").Offset(0, MY_INDEX - 1).Interior.ColorIndex = COLOR_FLAG Then Columns(MY_INDEX).Hidden = (Range("A1").Value = "HiDdEn") 'BOOLEAN
Next MY_INDEX
End Sub

 

[onlyadrafter]

Hello,

To find the last row used use this is code

mylastrow = Cells.SpecialCells(xlCellTypeLastCell).Row


It will not count the hidden rows at the end though. e.g. if you have data in A1:a10 and hide row 10, it will give a value of 9. Is this still OK?

for the column and row numbers you need these

ROW_NO = Target.Row
COL_NO = Target.Column

Is this adequate, as I seem to have lost track of what you are trying to achieve now.

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[CTruax]

Finding the maximum number of lines in a worksheet.

'won't find max if line is hidden
MaxCount = Cells.SpecialCells(xlCellTypeLastCell).Row

'won't find max unless there's data in last cell
MaxCount = Range("A65536").End(xlUp).Row

Is there a way to get the number of rows in a worksheet,
hidden and unused? Something like

MaxCount = ActiveWorkSheet.Rows