Hiding rows w/ "" cell and printing
 

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

Hiding rows w/ "" cell and printing
 

Steve, are you checking the formula or the value of $S254?
Range("S254").Formula = "" = False
Range("S254").Value = "" = True

Charles

Steve E wrote:
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

Hiding rows w/ "" cell and printing
 

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

Hiding rows w/ "" cell and printing
 

DAD!

I've tried this:

With ActiveSheet
On Error Resume Next
Range("S").Formula = "" = False
Range("S").Value = "" = True
.Columns("S").SpecialCells(xlCellTypeBlanks).Entir eRow.Hidden =
True
.PrintOut
.Columns("S").SpecialCells(xlCellTypeBlanks).Entir eRow.Hidden =
False
On Error GoTo 0
End With

Which clearly didn't work... the range I need to look at to be value="" is
S10:S258

I am still in the intro to "VB for Dummies" so need a simple solution if one
exists...

Thanks for the help!

"Die_Another_Day" wrote:

Steve, are you checking the formula or the value of $S254?
Range("S254").Formula = "" = False
Range("S254").Value = "" = True

Charles

Steve E wrote:
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

Hiding rows w/ "" cell and printing
 

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

Hiding rows w/ "" cell and printing
 

Steve, in a macro what you would need to write would be this:
Sub PrintNonBlank
Dim cnt as long
For cnt = 10 to 258
If Range("S" & cnt).Value = "" Then
Rows(cnt).Hidden = True
End If
Next
ActiveSheet.PrintOut
Rows("10:258").Hidden = False
End Sub

Charles

Steve E wrote:
DAD!

I've tried this:

With ActiveSheet
On Error Resume Next
Range("S").Formula = "" = False
Range("S").Value = "" = True
.Columns("S").SpecialCells(xlCellTypeBlanks).Entir eRow.Hidden =
True
.PrintOut
.Columns("S").SpecialCells(xlCellTypeBlanks).Entir eRow.Hidden =
False
On Error GoTo 0
End With

Which clearly didn't work... the range I need to look at to be value="" is
S10:S258

I am still in the intro to "VB for Dummies" so need a simple solution if one
exists...

Thanks for the help!

"Die_Another_Day" wrote:

Steve, are you checking the formula or the value of $S254?
Range("S254").Formula = "" = False
Range("S254").Value = "" = True

Charles

Steve E wrote:
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

Hiding rows w/ "" cell and printing
 

Charles,

It's pitiful I know... I added a control button to assign the macro to...
but I've got this wrong too....

Private Sub CommandButton1_Click()
Sub PrintNonBlank()
Dim cnt As Long
For cnt = 10 To 258
If Range("S" & cnt).Value = "" Then Rows(cnt).Hidden = True
End If
Next
ActiveSheet.PrintOut
Rows("10:258").Hidden = False
End Sub
End Sub

Also, should I expect to see the 'condensed' version when I view a print
preview?

Thanks!

Steve

"Die_Another_Day" wrote:

Steve, in a macro what you would need to write would be this:
Sub PrintNonBlank
Dim cnt as long
For cnt = 10 to 258
If Range("S" & cnt).Value = "" Then
Rows(cnt).Hidden = True
End If
Next
ActiveSheet.PrintOut
Rows("10:258").Hidden = False
End Sub

Charles

Steve E wrote:
DAD!

I've tried this:

With ActiveSheet
On Error Resume Next
Range("S").Formula = "" = False
Range("S").Value = "" = True
.Columns("S").SpecialCells(xlCellTypeBlanks).Entir eRow.Hidden =
True
.PrintOut
.Columns("S").SpecialCells(xlCellTypeBlanks).Entir eRow.Hidden =
False
On Error GoTo 0
End With

Which clearly didn't work... the range I need to look at to be value="" is
S10:S258

I am still in the intro to "VB for Dummies" so need a simple solution if one
exists...

Thanks for the help!

"Die_Another_Day" wrote:

Steve, are you checking the formula or the value of $S254?
Range("S254").Formula = "" = False
Range("S254").Value = "" = True

Charles

Steve E wrote:
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

Hiding rows w/ "" cell and printing
 

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

Hiding rows w/ "" cell and printing
 

Private Sub CommandButton1_Click()
Dim cnt As Long
For cnt = 10 To 258
If Range("S" & cnt).Value = "" Then Rows(cnt).Hidden = True
End If
Next
ActiveSheet.PrintOut
Rows("10:258").Hidden = False
End Sub

I removed the extra sub statements. That should fix it. And no you will not
see the appended version in print preview. You can comment out the
ActiveSheet.PrintOut and Rows("10:258").Hidden = False lines, then run the
macro, then check print preview if you want, or if that is really a needed
feature maybe Ron can come up with something more useable.

--
Charles Chickering

"A good example is twice the value of good advice."


"Steve E" wrote:

Charles,

It's pitiful I know... I added a control button to assign the macro to...
but I've got this wrong too....

Private Sub CommandButton1_Click()
Sub PrintNonBlank()
Dim cnt As Long
For cnt = 10 To 258
If Range("S" & cnt).Value = "" Then Rows(cnt).Hidden = True
End If
Next
ActiveSheet.PrintOut
Rows("10:258").Hidden = False
End Sub
End Sub

Also, should I expect to see the 'condensed' version when I view a print
preview?

Thanks!

Steve

"Die_Another_Day" wrote:

Steve, in a macro what you would need to write would be this:
Sub PrintNonBlank
Dim cnt as long
For cnt = 10 to 258
If Range("S" & cnt).Value = "" Then
Rows(cnt).Hidden = True
End If
Next
ActiveSheet.PrintOut
Rows("10:258").Hidden = False
End Sub

Charles

Steve E wrote:
DAD!

I've tried this:

With ActiveSheet
On Error Resume Next
Range("S").Formula = "" = False
Range("S").Value = "" = True
.Columns("S").SpecialCells(xlCellTypeBlanks).Entir eRow.Hidden =
True
.PrintOut
.Columns("S").SpecialCells(xlCellTypeBlanks).Entir eRow.Hidden =
False
On Error GoTo 0
End With

Which clearly didn't work... the range I need to look at to be value="" is
S10:S258

I am still in the intro to "VB for Dummies" so need a simple solution if one
exists...

Thanks for the help!

"Die_Another_Day" wrote:

Steve, are you checking the formula or the value of $S254?
Range("S254").Formula = "" = False
Range("S254").Value = "" = True

Charles

Steve E wrote:
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

Hiding rows w/ "" cell and printing
 

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

Hiding rows w/ "" cell and printing
 

Hi Steve

Remove Sub UnionExample() and the last end sub

Better use it like this
with the macro UnionExample in a normal module

Private Sub CommandButton1_Click()
Call UnionExample
ActiveSheet.PrintOut preview:=True
End Sub

If you want you can unhide the rows with the same button after printing


--
Regards Ron de Bruin
http://www.rondebruin.nl



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

Hiding rows w/ "" cell and printing
 

Ron,

Thank you so much for your help. I figured out what I was doing wrong and
everything is great now!

Really appreciate the 'real time' replies.

Steve

"Steve E" wrote:

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