Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i write a macro that will add rows into a spreadsheet | Excel Discussion (Misc queries) | |||
delete rows with empty cells | Excel Discussion (Misc queries) | |||
write a macro for variable cells | Excel Discussion (Misc queries) | |||
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? | Excel Worksheet Functions | |||
Macro to hide rows with empty cells | Excel Worksheet Functions |