hides rows

Enny Carolina Arevalo
New Contributor



If I have a list of 19000 data in a column and I want to hide every 10 cells in a column, how can I do it without do it manually?



4 Replies
Hi Enny

Are you saying you want to hide alternate rows of 10 ?
So row 1 - 10 show, row 11-20 hide, row 21 -30 show etc?

I would say that I want to hide from row 1-10 and row 11 is showing. Then from 12 till 20, row 21 is showing. And so on... Is there an option where I can manage the range of the cells to hide them? or do I have to do all manually?

if you put this formula in cell B1 and then copy it down, it will show the value 1 in each row you want to show, so then just filter for the 1s


Hi Enny,


You may use this macro to automate that task:

Sub HideSpecificRanges()
'Written by Haytham Amairah
'Created: 7/23/2018
'Last updated: 7/23/2018

    On Error GoTo handler
    Dim targetedRange As Range
    Set targetedRange = Application.InputBox("Please select a range of cells.", , , , , , , 8)
    Application.ScreenUpdating = False

    Dim cell As Range
    For Each cell In targetedRange
        If cell.Row Mod 10 <> 1 Then
           cell.EntireRow.Hidden = True
        End If
    Next cell
    Application.ScreenUpdating = True
'Exit sub

End Sub


Please give it a try in the attached file.

Also, follow this link to learn how to inject your own workbook with this code!


NOTE: This macro might take a few minutes until the loop finishes.



Related Conversations
How to SUM Data from non-regular rows
Andy_Przybysz in Excel on
1 Replies
Taking values from certain rows based on data input
Drakerla in Excel on
5 Replies