hides rows

Copper Contributor

Hello,

 

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?

 

Thanks!

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

=MOD(ROW(B1),10)

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
    
handler:
'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.

 

Regards