Set a variable as column range inside

Question

I have a named range for an entire table's column named DAY I have a macro that sets pagebreaks every time a cells value in the DAY column changes (like changes from day 1, to day 2, or day 3, there will be a page break for printing). However, this macro currently works by specifying the letter column like "A" or "B" or "C" or "H", how can I get it to work by specifying the "DAY" named range so if it moves, the vba code doesn't break?

Special attention to this:

For Each c In Range("C1:C" & lastrow)

Specifically:

Range("C1:C"

I want to change to:

Range("DAY"

but this breaks in various syntax forms I tried

Sub Set_PageBreaks_DAY()
Dim lastrow As Long, c As Range
Dim i As Integer, rngData As Range
Set rngData = Range("A1").CurrentRegion
i = Application.WorksheetFunction.Match("DAY", Range("A1:AZ1"), 0)
lastrow = Cells(Rows.Count, i).End(xlUp).Row
Application.ScreenUpdating = False
ActiveSheet.ResetAllPageBreaks
For Each c In Range("C1:C" & lastrow)
    If c.Offset(1, 0).Value <> c.Value And c.Offset(1, 0) <> "" Then
        c.Offset(1, 0).PageBreak = xlPageBreakManual
   End If
Next c
Application.ScreenUpdating = True
End Sub

Answer 1

First, it is important to note that Named ranges have 2 possible scopes which will affect how to access it. If your named range has workbook scope, then you should use

Dim Named_range_day as Range
Set Named_range_day = ThisWorkbook.Names("Day").RefersToRange

If the named range has worksheet scope, then use

Dim Named_range_day as Range
Set Named_range_day = wksht.Names("Day").RefersToRange

where wksht is the worksheet variable for the worksheet containing the named range.

Answer 2

The reason JLILI Aman's answer didn't work is you have to convert the column index number to a column letter first using

columnLetter = Split(Columns(i).Address(), "$")(2)

So for example

Sub Set_PageBreaks_CREW()

Dim lastrow As Long, c As Range
Dim i As Integer, rngData As Range
Set rngData = Range("A1").CurrentRegion
i = Application.WorksheetFunction.Match("DAY", Range("A1:AZ1"), 0)

lastrow = Cells(Rows.Count, i).End(xlUp).Row

Application.ScreenUpdating = False

ActiveSheet.ResetAllPageBreaks
columnLetter = Split(Columns(i).Address(), "$")(2)
Var = columnLetter & "1:" & columnLetter

For Each c In Range(Var & lastrow)
    If c.Offset(1, 0).Value <> c.Value And c.Offset(1, 0) <> "" Then
        c.Offset(1, 0).PageBreak = xlPageBreakManual
    End If
Next c

Application.ScreenUpdating = True

End Sub

Answer 3

Range("DAY").Resize(lastrow,1)

The above will reference the cell with name DAY and lastrow rows below it and in one column.

In general to reference a table of 100 rows and 5 columns with the top left at a cell, for example G2 use

Range("G2").Resize(100,5)

the above is entirely equivalent to

Range("G2:K101")

buy you don't have to do any of the weird string math with Range("G2:K" & count+1) etc.