Write a macro that hides rows based on empty cells

  • Thread starter Thread starter Mac0001UK
  • Start date Start date
M

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.
 
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
 
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
 
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
 
Back
Top