Recognize a pattern, then take an action

[TedX]

Howdy Friends, one of you will know how to do this instantly, most will scratch their head and end up in the "Stumped" mob, where I currently sit. As you can, I have a snippet of a worksheet showing and it has two horse races that follow a similar pattern. That pattern is as follows.

Race 1 always starts at "A6"
Columns C to R are always hidden.
At random, horses are hidden such as Row 22 and Row 31.
There is always the Race number followed by an empty row, followed by the time of the race, followed by an empty row, followed by the headings, "Tab", "Horse", etc.
These 5 rows are the patterns, Rows 6,7,8,9,10 and again for rows 26, 27, 28, 29, 30. There could be as many as 12 races on a day, and the number of horses changes in every race.

I want to either delete or hide the 2 empty rows in each pattern at the top of every race, so that the visible rows become 6, 8,10 and 26, 28, 30.

So the image above is how my race page currently looks and I would dearly love to have a macro that I could simply click, that would go from the top of the page down through each race, hiding or deleting the unwanted empty rows, so it would look similar to the example shown below.

Please keep in mind that there is a randomness to my pages in that the number of races changes daily and the number of horses per race also changes..... BUT ........ The pattern described above, never changes. First person with the solution is a genius.... Go!!!!!!

 

[Alex Blakenburg]

All roads lead to your dreaded loops

I have set it up to Hide, comment it out and uncomment the delete line if you want to delete.
If it does what you want and you want to see a version using a counter in the For loop instead using "For Each" let me know.

Sub DeleteOrHideRows()
    Dim ws As Worksheet
    Dim lastrow As Long
    Dim rngData As Range, rngHide As Range, rCell As Range

    Set ws = ActiveSheet
    
    With ws
        lastrow = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngData = .Range("A6:A" & lastrow)
    End With
    
    For Each rCell In rngData
        If rCell.Value = "Tab" Then
            If rngHide Is Nothing Then
                Set rngHide = Union(rCell.Offset(-3), rCell.Offset(-1))
            Else
                Set rngHide = Union(rngHide, rCell.Offset(-3), rCell.Offset(-1))
            End If
        End If
    Next rCell
    
    rngHide.EntireRow.Hidden = True
    'Alternative option to delete the rows
    'rngHide.EntireRow.Delete
    
End Sub

 

[Peter_SSs]

Another possible option

Sub Hide_Rows()
  Dim rFound As Range
  Dim fr As Long
  
  Set rFound = Columns("A").Find(What:="Tab", LookAt:=xlWhole)
  If Not rFound Is Nothing Then
    fr = rFound.Row
    Do
      Union(rFound(0), rFound(-2)).EntireRow.Hidden = True
      Set rFound = Columns("A").Find(What:="Tab", After:=rFound, LookAt:=xlWhole)
    Loop Until rFound.Row = fr
  End If
End Sub

 

[Peter_SSs]

All roads lead to your dreaded loops

.. unless you take a road less travelled (& assuming those blanks are truly empty).

Sub Hide_Rows_v2()
  With Range("A6", Range("A" & Rows.Count).End(xlUp))
    .SpecialCells(xlBlanks).Formula = "=match(""Tab"",offset(A$1,ROW(),,3),0)"
    .SpecialCells(xlFormulas, xlNumbers).EntireRow.Hidden = True
    .SpecialCells(xlFormulas).ClearContents
  End With
End Sub

 

[Alex Blakenburg]

Nice. I figured he is looking for somewhere to start in terms of coming to grips with loops based on previous posts and this isn't a bad example.

 

[TedX]

Okay, thank you gentleman, I have read everything you said, even the remarks about my amazing Phobia of loops that should probably be documented or made into a mini-series. Everything works perfectly and I was forced (you blokes know exactly what you're doing when you have multiple solutions), to select just one and give it the tick on the side. So drumroll please......

The winner is. . . . .

Peter_SSs who has come to my rescue on more than one occasion. I ended up using the solution in Post #3. Of course, I made a simple macro that switches to each of the 6 worksheets that are permanently named and called the Hide_Rows macro, which means that in less than an instant, all 6 race meetings have those 2 rows hidden. Thank you to both Peter and Alex, you guys are geniuses, not for knowing this stuff (yes that's impressive) but for the way you continue to help people endlessly. No need to draw any reader's attention to the fact that today is New Year's Day and all this help was forthcoming when it would have been quite reasonable not to have received a reply for days. The culture built up in Mr Excell is peerless, I have never encountered such wonderful people on the Internet ever before. Thank you again. I will one day help someone else, I promised that before and I'm just not having any luck finding someone dopier than me - which is a good thing Thanks again boys

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[Alex Blakenburg]

I want to either delete or hide the 2 empty rows

Thanks for letting us know. It sounds like you have decided to go with Hide rather than Delete.

 

[Peter_SSs]

It sounds like you have decided to go with Hide rather than Delete.

I had forgotten about that choice. I'm now assuming that the code would not be run unless at least one race existed so have removed the check that the first 'Find' actually found something. For deleting the rows the adaptation of my method would be ..

Sub Delete_Rows()
  Dim rFound As Range
  Dim fr As Long
  
  Set rFound = Columns("A").Find(What:="Tab", LookAt:=xlWhole)
  fr = rFound.Row
  Do
    Union(rFound(0), rFound(-2)).EntireRow.Delete
    Set rFound = Columns("A").Find(What:="Tab", After:=rFound, LookAt:=xlWhole)
  Loop Until rFound.Row < fr
End Sub

 

[TedX]

Thanks for letting us know. It sounds like you have decided to go with Hide rather than Delete.

Yes Alex, the reason being that I hide scratched horses in virtually every race, every day. In case you don't know what I mean, horses get nominated for a race by their trainers, and then the authority accepts some, which are called the Acceptances. Then as the race draws near and even on the race day itself, for any number of reasons, the connections of the horse want it withdrawn, this is called 'scratching' the horse. However because the databases and newspapers are all ready set with the Acceptances before the event, all the acceptances are shown, then if there is a scratching, it spoils the look of the race and you can actually waste time doing the form for the horse not realising it will be a non-starter. So I hide all these scratched horses using conditional formatting because the source of the data which gives me the race fields, colours the background of the cells in a dark colour, whilst the horses that are running have just basic white backgrounds. (No loops required)

So hiding works best for me because at the start of every new day, I unhide every hidden row. I guess it wouldn't make any difference except I keep hearing well-intentioned people telling me, never delete anything unless you absolutely have to. I guess that still rings in my head. I'm naturally tentative about hitting that old delete button. Thanks heaps Alex, I'm now one of your official stalkers, seeing you're from Australia