find headers

[QuietRiot]

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

 

[Dan Waterloo]

Some sample data would be very helpful to look at.
Secondly, what are you trying to do with it?

 

[pgc01]

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

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]

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]

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

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]

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]

ADVERTISEMENT

arg..

i just coded this POS

it works great but its sloppy

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]

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:

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

 

[Dan Waterloo]

Hi pgc01,

Your method makes so much sense. Thanks.