find headers

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077
right now I use one worksheet that contains 2 companys data.

say columnas A to F are for company 1 and I to j for company 2

however the headers are not always in the same spot.

i want to build something that will find this. Im looking for code that will give me "A:F" and "I:J"

it will always start with A so it needs to look for the a blank then stop and say hey that means A:F then continue find the next filled cell and start from there until the next blank to get I:J
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
Some sample data would be very helpful to look at.
Secondly, what are you trying to do with it?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi QuietRiot

Assuming the headers are in row 1, this code gives the range with the headers for the first table, A1:F1, in rHd1 and the headers for the second table, I1:J1, in rHd2

Code:
Dim rHd1 As Range, rHd2 As Range, rHd2Ini  As Range

Set rHd1 = Range("A1", Range("A1").End(xlToRight))
Set rHd2Ini = Range("A1").End(xlToRight).End(xlToRight)
Set rHd2 = Range(rHd2Ini, rHd2Ini.End(xlToRight))

Hope this helps
PGC
 

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077
I dont really see the point of sample data

its just row 1, columns filled then spaced out

header1, header2, header3 ,(blank), (blank) ,header1, header2, header3

that would be columns A:H company 1 would be A:C , company 2 would be F:H

and i need it for sorting purposes in another project. IE: sort A:H, then Sort A:C certain ways
 

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077

ADVERTISEMENT

Hi QuietRiot

Assuming the headers are in row 1, this code gives the range with the headers for the first table, A1:F1, in rHd1 and the headers for the second table, I1:J1, in rHd2

Code:
Dim rHd1 As Range, rHd2 As Range, rHd2Ini  As Range

Set rHd1 = Range("A1", Range("A1").End(xlToRight))
Set rHd2Ini = Range("A1").End(xlToRight).End(xlToRight)
Set rHd2 = Range(rHd2Ini, rHd2Ini.End(xlToRight))

Hope this helps
PGC

thanks PGC,

it im getting an error when I tested this though:
MsgBox rHd1
MsgBox rHd2
 

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
Yeah. Ranges aren't strings. Try these msgboxes instead.

MsgBox "Company A start column: " & rHd1.Column
MsgBox "Company A start row: " & rHd1.Row

MsgBox "Company A end column: " & rHd1.Columns(rHd1.Columns.Count).Column
MsgBox "Company A end row: " & rHd1.Rows(rHd1.Rows.Count).Row
 

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077

ADVERTISEMENT

arg..

i just coded this POS

it works great but its sloppy

Code:
Sub Macro1()
Dim hey As String, hey2 As String, hey3 As String

For v = 1 To 26
    If Cells(1, v).Value = "" Then
   hey = Mid(Cells(1, v - 1).Address, 2, 1)
    k = v
    v = 26
    End If
Next
For k = k To 26
    If Cells(1, k).Value <> "" Then
    hey2 = Mid(Cells(1, k).Address, 2, 1)
    
    h = k
    k = 26
    End If
Next
For h = h To 26
    If Cells(1, h).Value = "" Then
    hey3 = Mid(Cells(1, h - 1).Address, 2, 1)
    h = 26
    End If
Next


MsgBox "A:" + hey + " and " + hey2 + ":" + hey3



End Sub
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Again

it im getting an error when I tested this though:
MsgBox rHd1
MsgBox rHd2

Yes you are getting an error because, as Dan said, you can only use Msgbox with a string. What you tried is a syntax error.

You should instead display the addresses of the ranges:
Code:
Sub GetHeaderAddress()
Dim rHd1 As Range, rHd2 As Range, rHd2Ini  As Range

Set rHd1 = Range("A1", Range("A1").End(xlToRight))
Set rHd2Ini = Range("A1").End(xlToRight).End(xlToRight)
Set rHd2 = Range(rHd2Ini, rHd2Ini.End(xlToRight))

MsgBox rHd1.Address
MsgBox rHd2.Address
End Sub

Hope this helps
PGC
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

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
Top