Hiding Cells...

I attacked this problem with pivot tables.

First I converted the data on the data sheet to an Excel table and I added a helper column that checks to see if the Lane # cell is blank.

I then used this table as the data source for the pivot table on the Pivot 1 sheet. This pivot table gets the lane number, quantity, customer and comp where the lane number is not blank.

I then used this pivot table as a data source for a second pivot table. First I had to overlay it with a named dynamic range: Lane_Data =OFFSET('Pivot 1'!$A$3,0,0,COUNTA('Pivot 1'!$A:$A)-1,4).

Then I could use Lane_Data as the source for the second pivot table.

To get an understanding of named dynamic ranges, see this article: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges.

The second pivot table takes the right data (on Pivot 1) and formats it into the right format (on Pivot 2).

Well, if you insist .

Option Explicit

Public Sub HideCells()
Dim sh As Worksheet
Dim i As Long
Dim j As Long
Dim CountRange As Range

' initialize variables
Application.ScreenUpdating = False

Set sh = Sheets("cooler outilne")

'Unhide all rows and columns
sh.Cells.EntireRow.Hidden = False
sh.Cells.EntireColumn.Hidden = False

' Hide rows with no data
For i = 5 To 141
    Set CountRange = sh.Range(sh.Cells(i, "B"), sh.Cells(i, "BU"))
    If Count_Range(CountRange) = False Then
        CountRange.EntireRow.Hidden = True
    End If
Next

For j = 2 To 73
    Set CountRange = sh.Range(sh.Cells(5, j), sh.Cells(141, j))
 
    If Count_Range(CountRange) = False Then
        CountRange.EntireColumn.Hidden = True
    End If
Next
   
Application.ScreenUpdating = True
End Sub

Public Function Count_Range(MyRange As Range) As Boolean
Dim bRtn As Boolean
Dim cl As Range

bRtn = False

For Each cl In MyRange
    If Len(cl.Value) > 0 Then
        bRtn = True
        Exit For
    End If
Next

Count_Range = bRtn
End Function