Hiding Rows with Formulas

  • Thread starter Thread starter Ruan
  • Start date Start date
R

Ruan

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
 
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
 
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.
 
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
 
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
 
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 said:
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
Click to expand...
..Rows(Lrow).EntireRow.Hidden = True
 
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
 
Back
Top