VBA: Identify Header Row

[ExcelKnut]

I'm building a tool to accommodate various reports where the the header row could be in row 1, 2, 3 etc. This tool will be used by a number of different employees. I need a creative way (using VBA) to determine what row the header is in. Management has asked me to prevent the macro from running if there are less than 10 records on the report but each report has a header row and it could have a title etc.

BTW, users are only able to paste into cell A1.

 

[Scott T]

Need more information.

You could search for something that would only appear in the header. For example this will tell you the row number that "This will only be in the header" is in in column A

rnum = Application.Match("This will only be in the header", Range("A1:A3"), 0)

 

[ExcelKnut]

Scott, thank you for your suggestion.

Unfortunately, there's no predictable wording. These reports and their fields/headers are all different so there's no way for me to search for certain text.

 

[Scott T]

Will the header be the first non blank row?

rnum = Range("A1").End(xlDown).Row