Hiding rows programmatically

[Guest]

I would like to be able to hide consecutive rows in a workbook when the first row has the word Formula in Column A. The row with the word Formula in it will be contiguous with Data above it and may or may not have Rows with Data directly below it.
If possible, I would like to be able to unhide them as well. All such set of rows on a worksheet (there may be more than one Row starting with Formula per sheet) can be hidden and unhidden at the same time.

Any help would be greatly appreciated.

tj

 

[Tom Ogilvy]

Most of the code is from the help example for FindNext

dim c as Range, rng as Range
dim firstaddress as string
With Worksheets(1)
.Range(.Cells(1,1),.Cells(rows.count,1).end(xlup))
End with
with rng
Set c = .Find("Formula", lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
if isempty(c.offset(1,0)) then
c.Entirerow.Hidden = True
else
range(c,c.end(xldown)).EntireRow.Hidden = True
end if
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

--
Regards,
Tom Ogilvy

I would like to be able to hide consecutive rows in a workbook when the

first row has the word Formula in Column A. The row with the word Formula in
it will be contiguous with Data above it and may or may not have Rows with
Data directly below it.

If possible, I would like to be able to unhide them as well. All such set

of rows on a worksheet (there may be more than one Row starting with Formula
per sheet) can be hidden and unhidden at the same time.

 

[Guest]

Thanks!

tj

Most of the code is from the help example for FindNext

dim c as Range, rng as Range
dim firstaddress as string
With Worksheets(1)
.Range(.Cells(1,1),.Cells(rows.count,1).end(xlup))
End with
with rng
Set c = .Find("Formula", lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
if isempty(c.offset(1,0)) then
c.Entirerow.Hidden = True
else
range(c,c.end(xldown)).EntireRow.Hidden = True
end if
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

--
Regards,
Tom Ogilvy



first row has the word Formula in Column A. The row with the word Formula in
it will be contiguous with Data above it and may or may not have Rows with
Data directly below it.
of rows on a worksheet (there may be more than one Row starting with Formula
per sheet) can be hidden and unhidden at the same time.