PDA

View Full Version : Hide rows based on data in visible columns only.



RobG65
12-03-2008, 02:34 PM
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
12-03-2008, 03:18 PM
Hi RobG65,

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

RobG65
12-04-2008, 02:15 AM
Hi MaximS,

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

Cheers
Rob

RobG65
12-04-2008, 09:04 PM
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
12-04-2008, 09:38 PM
in that case use that code:


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

RobG65
12-07-2008, 04:24 AM
I finally got this to work, thanks MaximS for the clues I needed.

mdmackillop
12-07-2008, 05:02 AM
Rather than check each cell, use SpecialCells again

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

RobG65
12-07-2008, 01:16 PM
Thanks mdmackillop, neat way to execute the same task.