How to hide row range from code?

[Majstor]

Hello,

I use VB in Excel.
I want to hide part of Worksheet from row X to row Y.
X and Y should be integers marking rows where specific cell values (string
constants) were found.

In Excel VBA Help for me this is not clear enough. For example, how to
manipulate rows and their properties, it is only "range" and "cells".

Can someone give me an example?

Regards,
Vladimir

 

[Jim Rech]

Maybe this will help a little:

Dim StartRow As Long
Dim EndRow As Long
StartRow = 10
EndRow = 12
Rows(StartRow & ":" & EndRow).Hidden = True

 

[Chip Pearson]

Vladimir,

Try something like the following:


Dim StartRow As Long
Dim EndRow As Long
StartRow = 10
EndRow = 19
ActiveSheet.Rows(StartRow).Resize(EndRow - StartRow + 1).EntireRow.Hidden =
True


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[pfsardella]

Vladimir. Here's a start.

Sub HideRows()
Dim X As Integer, Y As Integer

X = Cells(1, 1).Value
Y = Cells(2, 1).Value
If X < 1 Or Y < 1 Then Exit Sub

Rows("" & X & ":" & Y & "").Hidden = True

End Sub

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul

 

[Majstor]

X and Y are in fact ordinal numbers of rows where specific string values S1
and S2 occur !
Let`s say that I find those rows with ".Find". How to return ordinal number
of row?

Vladimir

 

[Majstor]

X and Y are in fact ordinal numbers of rows where specific string values S1
and S2 occur !
Let`s say that I find those rows with ".Find". How to return ordinal number
of row?

Vladimir

 

[Guest]

Jim this seems to solve half of my problem! If I am using
the toggle button, what would the code be to allow the
first click to unhide the rows and then the second click
to hide the rows. The code below hides.

Janice

 

[Chip Pearson]

Vladimir,

You can do something like the following:

Dim X As Long
Dim Y As Long
Dim FoundCell As Range
Set FoundCell = Cells.Find(...)
X = FoundCell.Row
Set FoundCell = Cells.Find(...)
Y = FoundCell.Row


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[Tom Ogilvy]

Dim StartRow As Long
Dim EndRow As Long
StartRow = 10
EndRow = 12
Rows(StartRow & ":" & EndRow).Hidden = _
Not Rows(StartRow).Hidden