Macros to hide and unhide rows in a table of data which has separate subheadings

[AKOVA]

Hi guys,

Could someone please help me with this macro.

I have a table of data from row 129 to 226. in the table it has headings on rows 129, 149 and 215. I want to have two buttons one for Hiding rows with no values in Column D and one to unhide those columns back to normal but to not hide the headings rows.

I have tried and i cant work it out.

Thanks

Adrian

[JBeaucaire]

Like so:

Option Explicit

Sub UnhideRows()
    Range("D129:D226").EntireRow.Hidden = False
End Sub


Sub HideRows()
Dim MyRange As Range

On Error Resume Next
Range("D129:D226").EntireRow.Hidden = False

Set MyRange = Range("D130:D148,D150:D214,D216:D226").SpecialCells(xlBlanks)
If Not MyRange Is Nothing Then MyRange.EntireRow.Hidden = True

End Sub

[AKOVA]

Thanks for your reply but it seems to not be working. The screen is flicking but nothing is happening.

I have split the 2 codes above into separate modules. I did not however use the "Option Explicit". Was i supposed to?

Also there is a formula in column D that defaults to 0. Would this make a difference?

[AKOVA]

oops double post

[JBeaucaire]

EVERYTHING makes a difference. A cell with a formula in it is not "blank". Post a workbook and we'll take a look at what you really mean.

[AKOVA]

Sorry about that. Yes that is the only difference is that the D column is a total $ sum.

[AKOVA]

Ok. I modified the macro a bit and it seems to work but it works very slowly. Anyone know how to change what i did to work quicker?

Sub HideRows()
Dim MyRange As Range, MyCell As Range

On Error Resume Next
Range("D129:D226").EntireRow.Hidden = False

Set MyRange = Range("D130:D148,D150:D214,D216:D226")

If Not MyRange Is Nothing Then

For Each MyCell In MyRange
If MyCell.Value = "0" Then MyCell.EntireRow.Hidden = True
Next

End If

End Sub

[JBeaucaire]

I use Option Explicit at the top of ALL my modules. This flag basically turns on Excel's "code checker" and points out any variables I fail to declare or mispell, saving me hours of debugging. If you're going to use VBA a lot, I'd have this on all the time. Turn it on permanently here and it adds itself to any module you create/open for the first time:

[JBeaucaire]

Sub HideRows()
Dim MyRange As Range, MyCell As Range

Application.ScreenUpdating = False

Set MyRange = Range("D130:D148,D150:D214,D216:D226")

For Each MyCell In MyRange
    MyCell.EntireRow.Hidden = (MyCell.Value = "0")
Next MyCell

Application.ScreenUpdating = True
End Sub