Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - hiding rows given a cells content
Hello,
I am seeking some advice on a hiding rows using a macro. The spreadsheet has columns A-M and up to 2000 rows. The focus of my question is on column C. We have certain headings in column C that only appear once within that column but when they do we want to automatically hide that row and then go to the next heading which we have in our macro. For example, we have heading such as "ABC/XYZ" and "DEF/STU" and "FFF/GGG" which we would like to be included in the macro and automatically hide the row when that is contained in row C. But we have many other heading which we do not want to hide, or left out of the macro. Please help me as I have tried writing the string and I was unsuccessful. thanks The spreadsheet -- Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - hiding rows given a cells content
Sub HideCertainRows()
Dim varr as Variant, i as long, rng as Range varr = Array("ABC/XYZ", "DEF/STU", "FFF/GGG" ) for i = lbound(varr) to ubound(varr) set rng = Nothing set rng = columns(3).Find(varr(i)) if not rng is nothing then rng.Entirerow.Hidden = True end if Next End Sub -- Regards, Tom Ogilvy "Greg Stevens" wrote in message ... Hello, I am seeking some advice on a hiding rows using a macro. The spreadsheet has columns A-M and up to 2000 rows. The focus of my question is on column C. We have certain headings in column C that only appear once within that column but when they do we want to automatically hide that row and then go to the next heading which we have in our macro. For example, we have heading such as "ABC/XYZ" and "DEF/STU" and "FFF/GGG" which we would like to be included in the macro and automatically hide the row when that is contained in row C. But we have many other heading which we do not want to hide, or left out of the macro. Please help me as I have tried writing the string and I was unsuccessful. thanks The spreadsheet -- Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - hiding rows given a cells content
Substitute your headings you wish to hide (and add more) to th following: Sub Test1() Dim i As Long For i = 1 To Range("C65536").End(xlUp).Row 'Entire used C range If Range("C" & i).Value = "CCC" Or _ Range("C" & i).Value = "DDD" Or _ Range("C" & i).Value = "EEE" Then Range("C" & i).EntireRow.Hidde = True Next End Sub -- kkkni ----------------------------------------------------------------------- kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754 View this thread: http://www.excelforum.com/showthread.php?threadid=26967 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - hiding rows given a cells content
Thanks Tom, This works great. One supplemental question. Same spreadsheet
but now we want an additional macro in which if column A is empty and columns H and L are "0" then we would want to hide those rows. Thanks again. You are a big help. "Tom Ogilvy" wrote: Sub HideCertainRows() Dim varr as Variant, i as long, rng as Range varr = Array("ABC/XYZ", "DEF/STU", "FFF/GGG" ) for i = lbound(varr) to ubound(varr) set rng = Nothing set rng = columns(3).Find(varr(i)) if not rng is nothing then rng.Entirerow.Hidden = True end if Next End Sub -- Regards, Tom Ogilvy "Greg Stevens" wrote in message ... Hello, I am seeking some advice on a hiding rows using a macro. The spreadsheet has columns A-M and up to 2000 rows. The focus of my question is on column C. We have certain headings in column C that only appear once within that column but when they do we want to automatically hide that row and then go to the next heading which we have in our macro. For example, we have heading such as "ABC/XYZ" and "DEF/STU" and "FFF/GGG" which we would like to be included in the macro and automatically hide the row when that is contained in row C. But we have many other heading which we do not want to hide, or left out of the macro. Please help me as I have tried writing the string and I was unsuccessful. thanks The spreadsheet -- Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro for hiding rows | New Users to Excel | |||
Hiding Rows in a VBA macro | Excel Discussion (Misc queries) | |||
Macro for hiding rows | Excel Discussion (Misc queries) | |||
Macro for hiding rows | Excel Discussion (Misc queries) | |||
Hiding rows based on cell content | Excel Programming |