VBA: Set HeaderRowRange as a Range Variable

[zero269]

Hello,

I'm trying to set a dynamic variable using a Tables HeaderRowRange.

I figured if I can select the range using ActiveSheet.ListObjects(1).HeaderRowRange.Select that I could then store that selected range into a Range variable.

Sub SetRange()
 
ActiveSheet.ListObjects(1).HeaderRowRange.Select 'Select table Header Row
Dim header As Range
Set header = Range(Selection.Address)
 
MsgBox header
 
End Sub

I was hoping to get a range of A2:J2 in the active table, but when using the MsgBox to output the variable value, I get the following Run-time error:

I'm trying to find a dynamic way of setting this value so this code will work in other tables and sheets.

Any advice would be greatly appreciated.

 

[kevin9999]

Actually, you have already set the variable - you just can't show a 'range' in a message box like that. Try the following:

Sub GetHeadersAsVariable()
    Dim header As Range
    Set header = ActiveSheet.ListObjects(1).HeaderRowRange
    MsgBox header.Address
    header.Select
End Sub

 

[zero269]

Actually, you have already set the variable - you just can't show a 'range' in a message box like that. Try the following:

Sub GetHeadersAsVariable()
    Dim header As Range
    Set header = ActiveSheet.ListObjects(1).HeaderRowRange
    MsgBox header.Address
    header.Select
End Sub

Hi Kevin,

Thanks for getting back to me on this. I tested your code and it certainly works better than what I was trying to do. I was able to apply your solution to set my PrintArea and PrintTitles ranges for select print jobs.

Although the ranges return absolute values: $A$2:$J$108 for PrintArea and $A$2:$J$2 for PrintTitles, they still result in the correct output based on the Print Preview. My default values when not using vba are A2:J108 and $2:$2.

I tested this on a few other Sheets that contain only a single table, and the results are consistent based on the table sizes.

One thing for others to be aware of if they use this approach, is that the PrintTitles always defaults to the entire Row, so using this on Sheets where Tables are sitting beside each other may not work. This is true even without the use of VBA.

However, after running several tests across different Sheets and Tables, I discovered that what I'm doing is adding Named Ranges to my Name Manager. This will be problematic for me as the one table I print each week is always a different size… thus another entry in the Name Manager.

In the event that others may look to this as a solution for setting their PrintArea settings as well…

It looks like I'll need to remove these after each time the macro has completed, so I created another macro to clear the results, which I'll call on at the end of the print job.

'Set PrintArea and PrintTitles ranges
Sub PrintSelectedTable()
    
    Dim PrintArea As Range, PrintTitles As Range
    
    ActiveSheet.ListObjects(1).HeaderRowRange(1).Select 'Select HeaderRow
    Set PrintArea = ActiveSheet.ListObjects(1).Range
    Set PrintTitles = ActiveSheet.ListObjects(1).HeaderRowRange
    
    With ActiveSheet.PageSetup
            .PrintArea = PrintArea.Address
            .PrintTitleRows = PrintTitles.Address
    End With
    
    ActiveSheet.PrintOut Preview:=True

    Call ClearPrintAreas 'Clear PrintArea/PrintTitles from Name Manager

End Sub

'Clear PrintArea/PrintTitles from Name Manager
Sub ClearPrintAreas()

With ActiveSheet.PageSetup
            .PrintArea = ""
            .PrintTitleRows = ""
    End With
End Sub

Thanks again, Kevin, for all your help…

 

[kevin9999]

Thanks again, Kevin, for all your help…

Happy to help & thanks for the feedback