Hide rows based on data in visible columns only.

[RobG65]

Hi All,
Im trying to create a macro to hide rows that are empty based on the visible coulmns only. The hidden columns however may contain data.

So if columns b,c & d were hidden and had data, e,f & g were visible and empty I would like to hide that row.

Thanks in advance.
Rob

[MaximS]

Hi RobG65,

check attachment for possible solution. VBA code is located in Module1.

[RobG65]

Hi MaximS,

Thanks for that I will give it a go this evening.

Cheers
Rob

[RobG65]

I got it to work to a degree, what Ive noticed is it will hide a row at the discovery of the first blank cell. What I need it to do check the whole visible row is blank before hiding the row.

Sub Hide()

Dim LRow, LCol As Long

'This will check for last used row and column
LRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row - 1
LCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column - 1

'This will count rows
For i = 2 To LRow

'This will count columns
For j = 2 To LCol
If Cells(i, j).Value = "" And Columns(j).Hidden = False Then
Rows(i).Hidden = True
End If
Next j

Next i

End Sub

Thanks
Rob

[MaximS]

in that case use that code:

[VBA]
Sub Hidder()
Dim LRow, LCol, CBlanks, CHidden As Long
'This will check for last used row and column
LRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
LCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
For i = 1 To LRow
For j = 1 To LCol
If Cells(i, j).Value = "" And Columns(j).Hidden = False And _
Rows(i).Hidden = False Then
CBlanks = CBlanks + 1
End If
If Columns(j).Hidden = True Then
CHidden = CHidden + 1
End If
Next j
If LCol = CBlanks + CHidden Then
Rows(i).Hidden = True
End If
Next i
End Sub
[/VBA]

[RobG65]

I finally got this to work, thanks MaximS for the clues I needed.

[mdmackillop]

Rather than check each cell, use SpecialCells again
[vba]
Set Rng = Range(Cells(2, 2), Cells(LRow, LCol))
For Each Rw In Rng.Rows
If Application.CountA(Rw.SpecialCells(xlCellTypeVisible)) = 0 Then
Rw.EntireRow.Hidden = True
End If
Next
[/vba]

[RobG65]

Thanks mdmackillop, neat way to execute the same task.