CODEWITHSUNDEEP
excelvba
Aaron Morris
Aaron Morris

Reputation: 15

Hide 4 in every 5 rows

Starting from row 10, which I need to keep unhidden, I need to hide the next 4 rows and repeat that pattern until the last row containing data

ie. keep row 10, 15, 20, 25 and so on, potentially up to around row 300

This is to fit in a larger VBA macro within Excel so i dont need an entire Sub () just the middle section.

I think I need to find lastrow count rows from row 10 to last row Select row 10 then skip row, hide next 4 loop... For loop assume but I don't really know where to start

Upvotes: 1

Views: 307

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57743

I recommend the following using Step 5 in the For loop which should be faster than calculating the modulo for each i. And then hide the following 4 rows at once instead of one-by-one.

Dim ws As Worksheet
Set ws = ActiveSheet 'better define your sheet: ThisWorkbook.Worksheets("Sheet1")

Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

Dim iRow As Long
For iRow = 10 To LastRow Step 5 'means 10, 15, 20, 25 …
    ws.Rows(iRow + 1).Resize(RowSize:=4).EntireRow.Hidden = True
Next iRow

If you want to take better care of the last used row (don't hide any empty rows after LastRow use the following instead:

For iRow = 10 To LastRow - 1 Step 5 'means 10, 15, 20, 25 …
    If iRow + 5 > LastRow Then
        ws.Rows(iRow + 1).Resize(RowSize:=LastRow - iRow).EntireRow.Hidden = True
    Else
        ws.Rows(iRow + 1).Resize(RowSize:=4).EntireRow.Hidden = True
    End If
Next iRow

or even shorter (using IIf)

Dim ws As Worksheet
Set ws = ActiveSheet 'better define your sheet: ThisWorkbook.Worksheets("Sheet1")

Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

Const NthRow As Long = 5 'show only every Nth row

Dim iRow As Long
For iRow = 10 To LastRow - 1 Step NthRow 'means 10, 15, 20, 25 …
    ws.Rows(iRow + 1).Resize(RowSize:=IIf(iRow + NthRow > LastRow, LastRow - iRow, NthRow - 1)).EntireRow.Hidden = True
Next iRow

Upvotes: 4

riskypenguin
riskypenguin

Reputation: 2199

Try this:

Dim lngLR As Long
lngLR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 10 To lngLR
    If i Mod 5 <> 0 Then ActiveSheet.Rows(i).EntireRow.Hidden = True
Next i

Upvotes: 1

Related Questions