Hiding Rows with Formulas
 

Hello

I have an IF statement in Range("B9:B1000"), which either displays a value
or a blank cell. How do I hide the rows, if the result in this range is a
blank cell? The below formula doesn't work.

Sub HideRows()

With Range("B9:B1000")
.EntireRow.Hidden = False
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End With
Range("B9").Select
End Sub

Thanks
Ruan

Hiding Rows with Formulas
 

Your cells are not empty (formulas) that's why it is not working
You can loop and look for ""

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 9
EndRow = 1000
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "B").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "B").Value = "" Then .Rows(Lrow).EntireRow.Hidden = True

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ruan" wrote in message ...
Hello

I have an IF statement in Range("B9:B1000"), which either displays a value
or a blank cell. How do I hide the rows, if the result in this range is a
blank cell? The below formula doesn't work.

Sub HideRows()

With Range("B9:B1000")
.EntireRow.Hidden = False
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End With
Range("B9").Select
End Sub

Thanks
Ruan

Hiding Rows with Formulas
 

The below formula doesn't work
is not very explanatory. What doesn't work?

I just tested your MACRO and it worked just fine.
Since it is NOT a formula it must be inserted into a macro module and
executed.

--
Don Guillett
SalesAid Software

"Ruan" wrote in message
...
Hello

I have an IF statement in Range("B9:B1000"), which either displays a value
or a blank cell. How do I hide the rows, if the result in this range is a
blank cell? The below formula doesn't work.

Sub HideRows()

With Range("B9:B1000")
.EntireRow.Hidden = False
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End With
Range("B9").Select
End Sub

Thanks
Ruan

Hiding Rows with Formulas
 

Ruan,

I tested your code and it appears that the special cells method only
slects blanks before the last used cell in the range.
That seems strange to me, hopefully someone else can shed some light on
it.

In the meanwhile I suggest using filter/autofilter as a workaround.
Something like
Sub HideRows
if ActiveSheet.AutoFilterMode Then ' there is an autofilter
applied
ActiveSheet.Autofilter.Range.autofilter ' turn off filter
end if
[B9:B1000].AutoFilter Field:=1, Criteria1:="<" ' apply filter for
non-blanks
End Sub


---
Message posted from http://www.ExcelForum.com/

Hiding Rows with Formulas
 

Hello Don,

When I click on the Form Command Button that runs the code, I get an error
box that appears with just "400" displayed.

I have 2 Form Command Buttons. One to Hide Blank Rows, which is dependant on
range B9:B1000 having blank cells and the other button Unhides the Blank
Rows. I do this for Printing purposes, so that the User does not have to
print hundreds of blank rows.

Ruan


"Don Guillett" wrote in message
...
The below formula doesn't work
is not very explanatory. What doesn't work?

I just tested your MACRO and it worked just fine.
Since it is NOT a formula it must be inserted into a macro module and
executed.

--
Don Guillett
SalesAid Software

"Ruan" wrote in message
...
Hello

I have an IF statement in Range("B9:B1000"), which either displays a
value
or a blank cell. How do I hide the rows, if the result in this range is
a
blank cell? The below formula doesn't work.

Sub HideRows()

With Range("B9:B1000")
.EntireRow.Hidden = False
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End With
Range("B9").Select
End Sub

Thanks
Ruan

Hiding Rows with Formulas
 

Hello Ron,

Thanks for your help. If I am using a Form Command Control to activate the
code, is there no way to use the SpecialCells(xlCellTypeFormulas) method to
hide rows that display a blank value ""?


Sub HideRows()

With Range("B9:B1000")
.EntireRow.Hidden = False
.SpecialCells(xlCellTypeFormulas).EntireRow.Hidden = True
End With
Range("B9").Select
End Sub




"Ron de Bruin" wrote in message
...
Your cells are not empty (formulas) that's why it is not working
You can loop and look for ""

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 9
EndRow = 1000
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "B").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell

ElseIf .Cells(Lrow, "B").Value = "" Then
..Rows(Lrow).EntireRow.Hidden = True

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ruan" wrote in message
...
Hello

I have an IF statement in Range("B9:B1000"), which either displays a
value
or a blank cell. How do I hide the rows, if the result in this range is
a
blank cell? The below formula doesn't work.

Sub HideRows()

With Range("B9:B1000")
.EntireRow.Hidden = False
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End With
Range("B9").Select
End Sub

Thanks
Ruan

Hiding Rows with Formulas
 

Hi Ruan

A formula that evaluates to "" is not empty so you can't use xlCellTypeBlanks.
xlCellTypeFormulas will use all cells with a formula doesn't matter if it have a value or ""

You must loop through the cells like I show you

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ruan" wrote in message ...
Hello Ron,

Thanks for your help. If I am using a Form Command Control to activate the
code, is there no way to use the SpecialCells(xlCellTypeFormulas) method to
hide rows that display a blank value ""?


Sub HideRows()

With Range("B9:B1000")
.EntireRow.Hidden = False
.SpecialCells(xlCellTypeFormulas).EntireRow.Hidden = True
End With
Range("B9").Select
End Sub




"Ron de Bruin" wrote in message
...
Your cells are not empty (formulas) that's why it is not working
You can loop and look for ""

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 9
EndRow = 1000
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "B").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell

ElseIf .Cells(Lrow, "B").Value = "" Then
.Rows(Lrow).EntireRow.Hidden = True

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ruan" wrote in message
...
Hello

I have an IF statement in Range("B9:B1000"), which either displays a
value
or a blank cell. How do I hide the rows, if the result in this range is
a
blank cell? The below formula doesn't work.

Sub HideRows()

With Range("B9:B1000")
.EntireRow.Hidden = False
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End With
Range("B9").Select
End Sub

Thanks
Ruan

Hiding Rows with Formulas
 

Ron,

Your code works great. Thanks you so much.

Ruan


"Ron de Bruin" wrote in message
...
Hi Ruan

A formula that evaluates to "" is not empty so you can't use
xlCellTypeBlanks.
xlCellTypeFormulas will use all cells with a formula doesn't matter if it
have a value or ""

You must loop through the cells like I show you

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ruan" wrote in message
...
Hello Ron,

Thanks for your help. If I am using a Form Command Control to activate
the
code, is there no way to use the SpecialCells(xlCellTypeFormulas) method
to
hide rows that display a blank value ""?


Sub HideRows()

With Range("B9:B1000")
.EntireRow.Hidden = False
.SpecialCells(xlCellTypeFormulas).EntireRow.Hidden = True
End With
Range("B9").Select
End Sub




"Ron de Bruin" wrote in message
...
Your cells are not empty (formulas) that's why it is not working
You can loop and look for ""

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 9
EndRow = 1000
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "B").Value) Then
'Do nothing, This avoid a error if there is a error in
the
cell

ElseIf .Cells(Lrow, "B").Value = "" Then
.Rows(Lrow).EntireRow.Hidden = True

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ruan" wrote in message
...
Hello

I have an IF statement in Range("B9:B1000"), which either displays a
value
or a blank cell. How do I hide the rows, if the result in this range
is
a
blank cell? The below formula doesn't work.

Sub HideRows()

With Range("B9:B1000")
.EntireRow.Hidden = False
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End With
Range("B9").Select
End Sub

Thanks
Ruan