Automatically hide empty rows

[centprop]

Hi,

I have a clever workbook that creates reports for me. I didn't design this book, and am not all that experienced with VBA.

When it creates these report it hides empty rows to tidy the print layout.

I wanted to create a similar report to an already existing layout, adding an extra couple of columns. So, I copied the sheet with the report I want to modify, and added a couple of new columns and got the data going into the columns perfectly.

When you update the sheets, macros unhide all the previously hidden rows, populates the data, then hides unused rows again. In my new sheet, it unhides the previously hidden rows, but does not hide them again after populating the report with data.

If anyone can help me see what is going on I would be very grateful.

Code refferring to the original sheet that I am copying (called VacHoldingOSRR) looks like this:

Sub PortfolioUndatedGaps()  

Application.ScreenUpdating = False 
Application.Calculation = xlCalculationManual 
PortfolioUndatedGapsOpen 
PortfolioUndatedGapsClose 
Application.Calculation = xlCalculation
Automatic Application.ScreenUpdating = True  

End Sub

 Sub PortfolioUndatedGapsOpen()  

Application.ScreenUpdating = False  

Sheets("VacHoldingOSRR").Activate  
Range("UndatedEventCount").Offset(3, 1).Select 
Range(ActiveCell, ActiveCell.Offset(100, 0)).Select 
Selection.Rows.Hidden = False  

Range("A1").Select
 
End Sub

 Sub PortfolioUndatedGapsClose()  

Dim OffsetClose As Integer  

Application.ScreenUpdating = False  

OffsetClose = Range("UndatedEventCount").Value  

Range("UndatedEventCount").Offset(4, 1).Select  

If OffsetClose <> 0 Then      

ActiveCell.Offset(OffsetClose - 1, 0).Select  

End If  

Range(ActiveCell, ActiveCell.Offset(100 - OffsetClose - 1, 0)).Select
Selection.Rows.Hidden = True  

Range("A1").Select  

End Sub

I duplicated the code, renamed the macros, and made changes to the names of the named ranges so that they would match the new named ranges in the new sheet. So now the amended code for the new sheet looks like this:

Sub PortfolioUndatedGaps2()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
PortfolioUndatedGapsOpen2
PortfolioUndatedGapsClose2
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Sub PortfolioUndatedGapsOpen2()

Application.ScreenUpdating = False

Sheets("VacDocNeg").Activate

Range("UndatedEventCountvdn").Offset(3, 1).Select
Range(ActiveCell, ActiveCell.Offset(100, 0)).Select
Selection.Rows.Hidden = False

Range("A1").Select

End Sub

Sub PortfolioUndatedGapsClose2()

Dim OffsetClose As Integer

Application.ScreenUpdating = False

OffsetClose = Range("UndatedEventCountvdn").Value

Range("UndatedEventCountvdn").Offset(4, 1).Select

If OffsetClose2 <> 0 Then

    ActiveCell.Offset(OffsetClose - 1, 0).Select

End If

Range(ActiveCell, ActiveCell.Offset(100 - OffsetClose - 1, 0)).Select
Selection.Rows.Hidden = True

Range("A1").Select

End Sub

It almost works as expected, with one exception. It no longer hides the unused rows.

Can anyone see why and give me some help to fix it please?

Thanks in advance

Chris

 

[Trevor G]

This example will hide empty rows

Sub HideRows()
Application.ScreenUpdating = False
Dim i As Integer
Dim RStart As Range
Dim REnd As Range
Set RStart = Range("A2")
Set REnd = Sheets("YourSheetName").Range("A65536").End(xlUp).Offset(0, 3)
Range(RStart, REnd).Select
On Error Resume Next
With Selection
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.CountBlank(.Rows(i)) = 4 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
Set RStart = Nothing
Set REnd = Nothing
Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[centprop]

Thanks a lot for that.

I've not tried it yet. I really want to get this to work (lol stubborn)

I have realised however that all the macros above work as planned if ran manually. It seems that for some reason they aren't being activated automatically.

When I copied the sheet i wanted to modify, i adjusted the "Private Sub" also (Sorry, I dont know what these are known as). I realise these relate to the 2 macros listed in the OP, one being the original, and one being my amended version of PortfolioUndatedGaps. I assume these "Private Sub"s are a way of automatically running macro(s) when the sheet becomes active? SO it makes sense to me that this might be where my problem lies.


Here are the 2 to compare...

Original:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Start As Date

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("UndatedEvent")) Is Nothing Then

    GapHandling.PortfolioUndatedGaps
    
End If

End Sub

New:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Start As Date

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("UndatedEventvdn")) Is Nothing Then

    GapHandling.PortfolioUndatedGaps2
    
End If

End Sub

Thanks again in advance

Chris