Macro to hide rows in between 2 cells that contain certain text

[paspuggie48]

I have not done a thorough search of all VBA subjects in relation to Hide/Unhide certain cells etc but this is my problem in respect of a previous post I wrote. What I would like to do is create a macro with VBA and have it auto hide lines. However, my ranges change all the time but I do have fixed characters/text in certain cells. What I am thinking is if it finds these fixed text cells that a macro can then automatically hide the rows in between.

So....this is what I have in basic form. I'll write the Row number, then in Cell A1 is AAA, Cell A2 is 1, etc etc

Row 1 - AAA
Row 2 - 1
Row 3 - 2
Row 4 - 3
Row 5 - 4
Row 6 - 5
Row 7 - 6
Row 8 - 7
Row 9 - 8
Row 10 - BBB

Is it easy to write some VBA to say find the Cells that contain the text AAA and BBB and whatever cells are in between this range to have the macro auto hide those rows??

I ask this because as mentioned my ranges change, so another day BBB could be on Row 25, or 50 or whatever.

Any help would be much appreciated

Regards
Paul

 

[VoG]

TRy like this

Sub HideRows()
Dim F1 As Range, F2 As Range
Set F1 = Columns("A").Find(what:="AAA", LookIn:=xlValues, lookat:=xlWhole)
Set F2 = Columns("A").Find(what:="BBB", LookIn:=xlValues, lookat:=xlWhole)
If Not F1 Is Nothing And Not F2 Is Nothing Then Rows(F1.Row + 1 & ":" & F2.Row - 1).Hidden = True
End Sub

 

[paspuggie48]

Hey thanks Peter...let me try

 

[paspuggie48]

That is Brilliant !

I think I can apply that to loop or repeat for all the other ranges I have too...obviously if I can't, I know where you are

Thank you so much

 

[paspuggie48]

That worked a charm, thank you <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 11.25pt; HEIGHT: 11.25pt" id=_x0000_i1025 alt="0" type="#_x0000_t75"><v:imagedata src="file:///C:\DOCUME~1\PAUL~2.SPA\LOCALS~1\Temp\msohtml1\01\clip_image001.gif" o:href="http://www.mrexcel.com/forum/images/smilies/icon_smile.gif"></v:imagedata></v:shape><o></o>
<o></o>
I'm totally new to all VBA/Macro things, so I apologise for asking...My next question relates to a previous post and might, just might be a solution with the above format of code...<o></o>
<o></o>
In Cell B2 is certain criteria whereby I then want to do a "countif" function. So...

Col A (1,2,3 etc) Col B (X or Y)
AAA
1 ......X
2 ......X
3 ......X
4 ......Y
5 ......Y
6 ......X
7 ......X
8 ......X
BBB

Again, I would like to use the parameters of selecting the range from AAA to BBB and performing a countif function to determine X or Y between them, how would the code you provided be adapated to this?

Regards
Paul

 

[VoG]

Try this

Sub CountifRows()
Dim F1 As Range, F2 As Range, x As Long
Set F1 = Columns("A").Find(what:="AAA", LookIn:=xlValues, lookat:=xlWhole)
Set F2 = Columns("A").Find(what:="BBB", LookIn:=xlValues, lookat:=xlWhole)
If Not F1 Is Nothing And Not F2 Is Nothing Then Rows(F1.Row + 1 & ":" & F2.Row - 1).Hidden = True
If Not F1 Is Nothing And Not F2 Is Nothing Then x = WorksheetFunction.CountIf(Range("B" & F1.Row + 1 & ":B" & F2.Row - 1), "X")
MsgBox x
End Sub

 

[paspuggie48]

****, Peter...you are quick...I will play with this too, wish I knew what it all meant though LOL

 

[paspuggie48]

I love the message box coming up, I'll remember that...but I think I missed something out or maybe did nto explain myself properly. Like I say I'm new to all this.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
My previous post answered by Andrew was to perform the "countif" function based on certain criteria, this is great but my document has quite a lot of rows/columns to go through and is still a lot of work. But, I learnt something new and use it every day, to great effect.
<o></o>
I love the way your hidden formula works as it hides the 5000 lines I have on my document and I have adapated it to suit and can now see everything on the screen in one go. That is awesome. <o></o>
<o></o>
OK, the next step for me is to use that same range AAA and BBB and then countif the criteria (in this case, either X or Y...or whatever criteria I want to find). <o></o>
<o></o>
Once the hidden function is done I wanted another macro to perform a countif function. So, say I click on Cell C1, start the macro it would search for the range AAA to BBB, then put in the cell the figure for say "X" (which the answer is of course 8)<o></o>
<o></o>
Sorry for any inconvenience and your help is very much appreciated<o></o>
<o></o>
Regards<o></o>
paul<o></o>

 

[VoG]

So maybe like this

Sub CountifRows()
Dim F1 As Range, F2 As Range, x As Long
Set F1 = Columns("A").Find(what:="AAA", LookIn:=xlValues, lookat:=xlWhole)
Set F2 = Columns("A").Find(what:="BBB", LookIn:=xlValues, lookat:=xlWhole)
If Not F1 Is Nothing And Not F2 Is Nothing Then
    x = WorksheetFunction.CountIf(Range("B" & F1.Row + 1 & ":B" & F2.Row - 1), "X")
    ActiveCell.Value = x
    Rows(F1.Row + 1 & ":" & F2.Row - 1).Hidden = True
End If
End Sub

 

[paspuggie48]

I have adpated it to suit my spreadsheet and so far that is STUNNING ! I'm impressed

Thank you so much Peter