VBA: Set HeaderRowRange as a Range Variable

zero269

Active Member
Joined
Jan 16, 2023
Messages
253
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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:

1699740436057.png


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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Actually, you have already set the variable - you just can't show a 'range' in a message box like that. Try the following:
VBA Code:
Sub GetHeadersAsVariable()
    Dim header As Range
    Set header = ActiveSheet.ListObjects(1).HeaderRowRange
    MsgBox header.Address
    header.Select
End Sub
 
Upvote 1
Solution
Actually, you have already set the variable - you just can't show a 'range' in a message box like that. Try the following:
VBA Code:
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.

VBA Code:
'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

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

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

Thanks again, Kevin, for all your help…
 
Upvote 0

Forum statistics

Threads
1,223,974
Messages
6,175,739
Members
452,667
Latest member
vanessavalentino83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top