| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| How do display a "+" or "-" sign when hiding columns? | Setting up and Configuration of Excel | |||
| 2 questions one on list/combo boxes and the other on "atomically" hiding columns or rows. | New Users to Excel | |||
| Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
| Hiding "0" rows in printing. | Excel Programming | |||
Hybrid Mode
