CODEWITHSUNDEEP
vbaexcel
Abacus
Abacus

Reputation: 77

Excel macro to hide rows between variable row numbers

I'm trying to create a macro that will only show the top 20 rows and bottom 20 rows of a table of data and hide all rows between the 20th and n-19th rows.

The top row is not a static row - it can be identified as the the next row following the one that has the value "Company" in column B.

The last row is also not static as the total number of rows of data will vary over time.

Unfortunately, I'm not familiar with vba and this logic is proving very difficult to achieve. Is anyone able to help with how to get a macro to do this?

Any and all help is much appreciated!

Upvotes: 0

Views: 1593

Answers (2)

Gary's Student
Gary's Student

Reputation: 96771

This starts by hiding them all and then unhides the top and bottom:

Sub HideAndSeek()

    n1 = Columns("B").Find("Company").Row + 1
    n2 = Cells(Rows.Count, "B").End(xlUp).Row

    Rows(n1 & ":" & n2).EntireRow.Hidden = True

    For i = 0 To 19
        Cells(i + n1, "B").EntireRow.Hidden = False
        Cells(n2 - i, "B").EntireRow.Hidden = False
    Next i
End Sub

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

This should work:

Sub hide_in_between()
Dim ws      As Worksheet
Dim startCell As Range, endCell As Range

Set ws = Worksheets("Sheet1")    ' CHANGE as necessary

With ws
    Set startCell = .Range("B:B").Find(what:="Company").Offset(1, 0)
    Set endCell = .Range("B" & .Rows.Count).End(xlUp)

    Dim topGroup As Range, bottomGroup As Range

    Set topGroup = .Range(startCell, startCell.Offset(19, 0))
    Set bottomGroup = .Range(endCell.Offset(-19, 0), endCell)

    Dim hiddenGroup As Range
    Set hiddenGroup = .Range(topGroup.Rows(topGroup.Rows.Count), bottomGroup.Rows(1).Offset(-1, 0))

    hiddenGroup.EntireRow.Hidden = True
End With

End Sub

It's relatively straightforward, if not a little verbose (it can probably be shortened). However, for learning, this should help a lot. Please note that you

Upvotes: 0

Related Questions