Hi Ron,
Welll.... I think I'm getting closer...
I tried to assign the code to a control button to "condense and print" my
BoM. When I 'click' now I get a complie error immediately preceeding the Sub
UnionExample() line...
Ideally, I'd like to be able to click on this button to condense the report
and then print it... and have a seperate button to click to 'undo' the
earlier condensation so that I can restore the entire report...
Your patience is remarkable and much appreciated.
Public Sub CommandButton1_Click()
Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 10
EndRow = 273
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, "S").Value) Then
'Do nothing, This avoid a error if there is a error in
the cell
' is comment break point supposed to be before the Do Nothing, or after?
ElseIf .Cells(Lrow, "S").Value = "" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "S")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "S"))
End If
End If
Next
End With
' Hide all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
End Sub
"Ron de Bruin" wrote:
Hi Steve
This example you can use (original code for deleting rows)
http://www.rondebruin.nl/delete.htm#Union
This example check the cells in row 1 -100 in A
Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf .Cells(Lrow, "A").Value = "" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "A")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "A"))
End If
End If
Next
End With
Hide all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Steve E" wrote in message ...
Hi Ron,
EasyFilter is great!
I'd like to have a macro or sheet code to do this so that I don't have to
worry about whether or not my user has installed the EasyFilter addin... can
you help?
Thanks!
Steve
"Ron de Bruin" wrote:
Hi Steve
My EasyFilter add-in have a option to filter for formulas that not are ""
http://www.rondebruin.nl/easyfilter.htm
If you want to use a macro post back
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Steve E" wrote in message ...
I have a master Bill of Materials with about 2750 possible rows but generally
only have contents in about 30 rows and want to hide all of the rows with no
value in a cell and then print the entire range (after the rows are hidden)
and then unhide the rows.
Print Range is:$A$1:$V$280
The cell that should control the action is in col "s" and contains a formula
like:
$S254=IF((SUM($D254:$R254)*FabricWasteFactor)=0,"" ,(SUM($D254:$R254)*FabricWasteFactor))
The $D:$R column range is the line item detail quantities.
When $S254 evaluates to "" I want to hide that row (for rows 10 thru 258)
I tried to use code that I found on Ron DeBruin's web site but couldn't get
it to work (I think because I have a formula in the cell so it is not truly
blank...) and can't figure out what I'd have to change to make it work and I
can't find another post that is 'on point'
All help is appreciated!
Steve