Write a macro that hides rows based on empty cells
 

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.
--
Mac Macdonald

Write a macro that hides rows based on empty cells
 

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


"Mac0001UK" wrote:

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.
--
Mac Macdonald

Write a macro that hides rows based on empty cells
 

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

"Mac0001UK" wrote:

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.
--
Mac Macdonald

Write a macro that hides rows based on empty cells
 

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

--
Regards
Roger Govier

"Mac0001UK" wrote in message
...
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.
--
Mac Macdonald