macro - hiding rows given a cells content

[Guest]

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

 

[Tom Ogilvy]

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

 

[kkknie]

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

 

[Guest]

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]

Option Explicit
Sub HideEmpty()
Dim cell As Range, rng1 As Range
Dim bYesH As Boolean, bYesL As Boolean

With ActiveSheet
Set rng1 = Intersect(.Columns(1), .UsedRange).Cells
End With
For Each cell In rng1
If IsEmpty(cell) And _
cell.Offset(0, 7).Text = "0" And _
cell.Offset(0, 11).Text = "0" Then
cell.EntireRow.Hidden = True
End If
If IsEmpty(cell) And _
IsNumeric(cell.Offset(0, 7)) And _
IsNumeric(cell.Offset(0, 11)) And _
Not IsEmpty(cell.Offset(0, 7)) And _
Not IsEmpty(cell.Offset(0, 11)) Then
If cell.Offset(0, 7) = 0 And _
cell.Offset(0, 11).Value = 0 Then
cell.EntireRow.Hidden = True
End If
End If

Next cell
End Sub



This could be more compact with more knowledge of your data, but should do
what you want. Also, there are some restrictions in Excel 97 which requires
some separate checks - so this should work with xl97 or later.