Using VBA to hide row and row below and VBA to unhide all aswell

[anilsss]

I have a range of Names in cell C19:C420 that I want to rank by sales, I have created a sheet that is showing all my data. Cells in C are merged together, for example, C19 and C20 are merged together to show a persons name. This carry's on up to the end of the data set.

the persons name in column C is derived from an IF statement. if it matches the criteria it will bring in the name, if not it will show "BLANK".

I need a VBA formula to go through the range C19:C420 in the sheet and hide cells with "BLANK" as well as the cell below it and then carry on through the range until it gets to the next blank and does the same until it gets to the end of the range.

Along with this I also need a VBA code to unhide all the hidden cells in that range with a click of a button I'm looking to place in the sheet.

I don't really know anything about writing a VBA code so the help would be grateful.

Thank you in advance.

 

[Truiz]

Try the following

Sub HideRows
  Dim iCntr As Long
    
    For iCntr = 420 To 19 Step -1    If Cells(iCntr, "C") = "BLANK" Then
    Rows(iCntr).EntireRow.Hidden = True
    End If
    Next

End Sub

 

[Fluff]

Alternatively this will toggle the hidden property of the rows

Sub HideRows()

  Dim Cnt As Long
    
    For Cnt = 19 To 420
        If UCase(Range("C" & Cnt).Value) = "BLANK" Then
            With Rows(Cnt).Resize(2).EntireRow
                .Hidden = Not .Hidden
            End With
        End If
    Next

End Sub

 

[Truiz]

I forgot the unhide button

Sub Macro1()Dim rngStart As Range


Set rngStart = ActiveCell
    With Cells
        .EntireColumn.Hidden = False
        .EntireRow.Hidden = False
    End With
    
rngStart.Select
End Sub

 

[anilsss]

Hi Guys,

Thanks for the quick response.

Truiz, I tried your VBA code and I receive a syntax error on the following line "For iCntr = 420 To 19 Step -1 If Cells(iCntr, "C") = "BLANK" Then" (without quotation marks).

Fluff, I tried yours too but it doesn't seem to do anything with the sheet.

Any help again would be great!

Thanks in advance!

 

[anilsss]

Forgot to mention, column C has a formula in there to bring back blank.

Not sure if this is affecting the macro:

=IFERROR(IF(INDEX(Sheet1!$T$2:$T$1000,A11,1)="PERSON",INDEX(Sheet1!$B$2:$B$1000,A11,1),"BLANK"),"BLANK")

 

[Fluff]

Fluff, I tried yours too but it doesn't seem to do anything with the sheet.

Not quite sure why it's not working for you. I've tried it with the formula you supplied in post#6 & it works fine for me.
Have you put he code in a standard module?
How are you running it?

 

[Fluff]

Additionaly, with my code, the first time it is run the rows should be hidden, the second time it is run the rows will become visible.

 

[anilsss]

Thanks Fluff,

My mistake, my data set started at row 9 not 19. I amended that bit of the code and now it looks like its working a treat, have no idea how its doing it but it's doing it. Thanks!

 

[Fluff]

Glad to help & thanks for the feedback

.Hidden is a property of the row & is always either False (if the row is visible) or True (if the row is hidden).
So this line

.Hidden = Not .Hidden

is swapping that value.
ie if the row is visible .hidden is False & therefore Not .hidden is True
HTH