help with hiding rows when a cell is not blank

[Oberon70]

Hi,

I am trying to hide rows in the below code, but for some reason is it not working.

Case 59 is the line I am wanting to use to hide rows if a cell is not blank.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim hr As Double
hr = ActiveCell.Row
Debug.Print hr

  ' If Sheets("Rapid Receipting").Range("AB57") <> "" Then Rows("1:" & hr).EntireRow.Hidden = True
    
    If Intersect(Target, Columns("AA")) Is Nothing Then Exit Sub 'stop if you ddin't right click in the AA-column

     'later instead of that msgbox, you do target.offset(......).COPY

     Select Case Target.Row Mod 60 'depending on the rownumber (modulus 60 !!), choose a range
          Case 2: Target.Offset(, 2).Resize(16, 2).Copy
          Case 19 To 20: Target.Offset(, 2).Resize(1, 47).Copy
          Case 21: Target.Offset(, 2).Resize(1, 31).Copy
          Case 24, 37: Target.Offset(, 2).Resize(12, 3).Copy
          Case 50: Target.Offset(, 2).Resize(8, 3).Copy
          Case 59: If Range("AC59").Value <> "" Then Rows("1:" & Target.Offset(, 2).Row).EntireRow = Hidden
                    Case Else: Exit Sub
     End Select
End Sub

 

[Alex Blakenburg]

The way you currently have it the code should be crashing.

Change your Case 59 to this

              Case 59: If Range("AC59").Value <> "" Then Rows("1:" & target.Offset(, 2).Row).EntireRow.Hidden = True

 

[Oberon70]

Okay, I have done a work around that works for me.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
     If Intersect(Target, Columns("AA")) Is Nothing Then Exit Sub 'stop if you ddin't right click in the AA-column

     'later instead of that msgbox, you do target.offset(......).COPY

     Select Case Target.Row Mod 60 'depending on the rownumber (modulus 60 !!), choose a range
          Case 58: Rows("1:" & ActiveCell.Row).EntireRow.Hidden = True
          Case Else: Exit Sub
     End Select

     Cancel = True 'cancel the normal popup for right-clicking
    
End Sub

But I will also try what you have written Alex thanks

 

[BSALV]

 Case 59: If Range("AC59").Value <> "" Then Rows("1:" & Target.Offset(, 2).Row).EntireRow = Hidden

because of that "Select Case Target.Row Mod 60", the cell you're looking at isn't always row 59, but can also be every multiple 60+59, so 59, 119,179,239,299,...
so it has to be something like
Case 59: If Range("AC" & target.row).Value <> "" Then Rows(target.row-58 & ":" & Target.Offset(, 2).Row).EntireRow = Hidden

 

[Alex Blakenburg]

But I will also try what you have written Alex thanks

You have already made that change in the code you showed in that same post (#3).
If you adopt @BSALV's Case 59 code change in the above post you will need to apply the same amendment, it is using your original incorrect code.