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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:%[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> 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" <[email protected]> wrote in message news:%[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> 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
> >
> >
>
>
>