Write a macro that hides rows based on empty cells

[Mac0001UK]

I am working with Excel 2007. I would like to write a macro that hides rows
based on an empty cell in that row, the same cell in each row.
It would have to be able to look so far down a spreadsheet hiding rows with
that empty cell, C??, then continue on beyond that with cells containing
data, then hiding rows after that with the empty C cell.
I would be most grateful if anyone can help.

 

[JLatham]

Give this macro a try.

Sub HideRows()
'hides rows on the selected sheet
'that have empty cells in the
'column identified as keyColumn

Const keyColumn = "C"
Dim usedInKeyColumn As Range
Dim anyCellInKeyColumn As Range

Set usedInKeyColumn = Range(keyColumn & _
"1:" & Range(keyColumn & _
Rows.Count).End(xlUp).Address)
Application.ScreenUpdating = False
'start by unhiding them all
usedInKeyColumn.EntireRow.Hidden = False
'now hide ones with empty cells
For Each anyCellInKeyColumn In usedInKeyColumn
If IsEmpty(anyCellInKeyColumn) Then
anyCellInKeyColumn.EntireRow.Hidden = True
End If
Next
Set usedInKeyColumn = Nothing
End Sub

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub Hide_me()
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Set MyRange = Range("C1:C" & LastRow)
For Each c In MyRange
If IsEmpty(c) Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.EntireRow.Hidden = True
End If
End Sub


Mike

 

[Roger Govier]

Hi Mac

You could just use Autofilter on column C, selecting Non Blanks.
To do that programmatically

Sub HideRows()
Dim ws As Worksheet

Set ws = ActiveSheet
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
If ws.AutoFilterMode = False Then
ws.Range("A1").AutoFilter
End If
Selection.AutoFilter Field:=3, Criteria1:="<>" & ""
End Sub