Get the Name of the Named Range a Cell is Located in Using VBA

[WCrawford]

I have a spreadsheet that consists of 12 months (1 one on each sheet). On each sheet there are 29 to 31 Named Ranges, referencing each day of the month (i.e., "JanRange01" etc.). And each Named Range consists of 16 rows. In the first column of each range there is merged cell of all 16 rows, which lists a date in the format of "Monday, January 1, 2024".

What I need to do is to hide the Named Range for every date that represents a Sunday. I know the VBA code for hiding the row of the cell that contains the actual date formula (first cell in the merged cells), but I need to hide the entire range where the particular date cell is located.

Any help with this matter would be greatly appreciated.

 

[kevin9999]

Welcome to the Forum!

Please try the following on a copy of your workbook. Had to assume (because your image isn't clear on this) that your dates are in column A, and your first merged area starts on row 3 - adjust if that's not the case.

Option Explicit
Sub Hide_Sundays()
    Dim ws As Worksheet, c As Range
    For Each ws In ThisWorkbook.Worksheets
        For Each c In ws.Range("A3:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
            If Weekday(c.Value) = 1 Then c.MergeArea.EntireRow.Hidden = True
        Next c
    Next ws
End Sub

In future posts, it would be useful if you could provide your example using the XL2BB add in, which would help enormously.

 

[Macropod]

You really don't need to know anything about the names of the ranges. Since each range spans 16 rows, all you need to know is which row has the date. You can then test whether the date is a Sunday via a simple MOD test, then hide all 16 rows relating to that date. For example:

Sub Demo()
Application.ScreenUpdating = False
Dim Lrow As Long, r As Long
With ActiveSheet
  Lrow = .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row - 15
  For r = Lrow To 1 Step -16
    If .Range("A" & r).Value Mod 7 = 1 Then
      .Range("A" & r & ":A" & r + 15).EntireRow.Hidden = True
    Else
      .Range("A" & r & ":A" & r + 15).EntireRow.Hidden = False
    End If
  Next
End With
Application.ScreenUpdating = True
End Sub

 

[WCrawford]

Kevin9999,

The code you provided, with some minor changes [see code below] (i.e., A3:A to B7:B, and "A" to "B"), works perfectly. But there is one thing I forgot to mention. In order to keep the display of the worksheet consistent (no double black rows where Sundays have been removed), it's imperative that the hidden area start one (1) row above the cell where the date is located, and then down for a total of 17 rows.

EXAMPLE: For "Sunday, January 7, 2024", the hidden area is currently rows 109 through 124. And what I need it to be is 108 through 124. The row above where the date is located (in this example, the date is located in "B109"), and then for a total of 17 rows (109 - 124)

Can you please modify the code below to make those changes. It's been a while since I've used VBA, and I'm rustier than I thought.

Thank you for your time.

Sub Hide_Sundays()
    Dim ws As Worksheet, c As Range
    For Each ws In ThisWorkbook.Worksheets
        For Each c In ws.Range("B7:B" & ws.Cells(Rows.Count, "B").End(xlUp).Row)
            If Weekday(c.Value) = 1 Then c.MergeArea.EntireRow.Hidden = True
        Next c
    Next ws
End Sub

 

[Macropod]

With the code I posted, that would be handled with something like:

Sub Demo()
Application.ScreenUpdating = False
Dim Lrow As Long, r As Long
With ActiveSheet
  Lrow = .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row - 15
  For r = Lrow To 1 Step -16
    If .Range("A" & r).Value Mod 7 = 1 Then
      .Range("A" & r - 1 & ":A" & r + 15).EntireRow.Hidden = True
    Else
      .Range("A" & r - 1 & ":A" & r + 15).EntireRow.Hidden = False
    End If
  Next
End With
Application.ScreenUpdating = True
End Sub

Note: something I didn't point out before is that my code ensures other rows aren't hidden. If you don't need that, you could delete:

    Else
      .Range("A" & r-1 & ":A" & r + 15).EntireRow.Hidden = False

 

[Peter_SSs]

What about this?

Sub Hide_Sundays()
  Dim ws As Worksheet
  Dim r As Long
  
  For Each ws In Worksheets
    For r = 7 To 517 Step 17
      ws.Rows(r - 1).Resize(17).Hidden = ws.Range("B" & r).Text Like "Sun*"
    Next r
  Next ws
End Sub