macro to hide rows if cell is blank
 

My spreadsheet range is A1:F50. The cells in column D are either empty or
have the letter M. I would like to create a macro to hide all rows where cell
D is blank, in other words only the rows with the letter "M" in column D will
be visible. I appretiate your help with code to do this. Thanks.


--
Shooter

Why don't you use AutoFilter ?

Try this one if you want a macro solution

You can use this to hide all the cells with a 0 in column A in the first 100 rows of the active sheet.

Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 50
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, "D").Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf .Cells(Lrow, "D").Value = "0" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "A")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "D"))
End If
End If
Next
End With
'hide all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Hidden = True

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




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


"Shooter" wrote in message ...
My spreadsheet range is A1:F50. The cells in column D are either empty or
have the letter M. I would like to create a macro to hide all rows where cell
D is blank, in other words only the rows with the letter "M" in column D will
be visible. I appretiate your help with code to do this. Thanks.


--
Shooter

Sorry you say blank

You can use

ElseIf .Cells(Lrow, "D").Value = "" Then

Or use the VBA IsEmpty function in the code



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


"Ron de Bruin" wrote in message ...
Why don't you use AutoFilter ?

Try this one if you want a macro solution

You can use this to hide all the cells with a 0 in column A in the first 100 rows of the active sheet.

Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 50
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, "D").Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf .Cells(Lrow, "D").Value = "0" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "A")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "D"))
End If
End If
Next
End With
'hide all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Hidden = True

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




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


"Shooter" wrote in message ...
My spreadsheet range is A1:F50. The cells in column D are either empty or
have the letter M. I would like to create a macro to hide all rows where cell
D is blank, in other words only the rows with the letter "M" in column D will
be visible. I appretiate your help with code to do this. Thanks.


--
Shooter

All you need is:

Range("D1:D50").AutoFilter field:=1, Criteria1:="M"

To later restore the sheet to its unfiltered state:

Range("D1:D50").AutoFilter

"Ron de Bruin" wrote:

Sorry you say blank

You can use

ElseIf .Cells(Lrow, "D").Value = "" Then

Or use the VBA IsEmpty function in the code



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


"Ron de Bruin" wrote in message ...
Why don't you use AutoFilter ?

Try this one if you want a macro solution

You can use this to hide all the cells with a 0 in column A in the first 100 rows of the active sheet.

Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 50
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, "D").Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf .Cells(Lrow, "D").Value = "0" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "A")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "D"))
End If
End If
Next
End With
'hide all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Hidden = True

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




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


"Shooter" wrote in message ...
My spreadsheet range is A1:F50. The cells in column D are either empty or
have the letter M. I would like to create a macro to hide all rows where cell
D is blank, in other words only the rows with the letter "M" in column D will
be visible. I appretiate your help with code to do this. Thanks.


--
Shooter