Select & hide multiple rows based on cell value

[tribtown]

I have attached the file I'm working on (it's not my file, I just have to prepare it to print). I'm pretty new to VBA but I have a macro that will hide all the blank rows and another to unhide them all but I can't figure out how to get the rest of the way to what I want to accomplish.

Tab A is the base that I'm working with. Tab B s what I want it to end up as.

Here's what I think I'm trying to make happen:
1) Start at the top of column C
2) Move down until left 4 digits are 2021
3) Select all rows until right 4 digits in column F are 2022
4) Move up 1 row (to leave row with Account # visible)
5) Hide selected rows
6) Loop until no more cells in C start with 2021

[Paul_Hossler]

Welcome to the forum - please take a minute and look over the FAQs at the link in my sig

Try something like this



Option Explicit


Sub Macro1()
Dim ws As Worksheet
Dim cnt2021 As Long, cnt2022 As Long
Dim ary2021() As Long, ary2022() As Long
Dim rowLast As Long, i As Long

Set ws = Worksheets("A")
rowLast = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row


For i = 1 To rowLast
If Left(ws.Cells(i, 3).Value, 4) = "2021" And ws.Cells(i + 1, 3) <> "Jan" Then
cnt2021 = cnt2021 + 1
ReDim Preserve ary2021(1 To cnt2021)
ary2021(cnt2021) = i
End If
Next i


For i = 1 To rowLast
If Right(ws.Cells(i, 6).Value, 4) = "2022" And ws.Cells(i + 1, 6) <> "Apr" Then
cnt2022 = cnt2022 + 1
ReDim Preserve ary2022(1 To cnt2022)
ary2022(cnt2022) = i
End If
Next i

i = 1
Do While Len(ws.Cells(i, 3).Value) = 0
ws.Rows(i).Hidden = True
i = i + 1
Loop



For i = 1 To UBound(ary2021)
ws.Rows(ary2021(i)).Resize(ary2022(i) - ary2021(i)).Hidden = True
' ws.Cells(ary2022(i), 6).ClearContents ' ??????????????
Next i
End Sub

[tribtown]

Thank you Paul!!