selecting a range with an unknown col end

[KNKN9]

Hi and thanks in advanced.

I have the data set

ColA ColB ColC ColD ColE ColF ColG ColH ColI
Row 1 x blank cell x blank cell X blank cell x blank cell

I want to select the range ("A1:I1") however the data may not always end in column I so for another sheet the data may end in col Q.

Is there a code that can select this range based on the data in row 1?

 

[steve the fish]

See if this is what you mean? The range is assigned to the variable rng.

With Sheets("Sheet1")
    Set rng = .Range(.Cells(1, 1), .Cells(1, .Cells(1, .Columns.Count).End(xlToLeft).Column))
End With

 

[Fluff]

If the last column in row1 does not always have a value you could use

Sub KNKN9()
   Dim UsdCols As Long
   UsdCols = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
End Sub

 

[KNKN9]

This works exactly what I am after !
Thank you !

See if this is what you mean? The range is assigned to the variable rng.

With Sheets("Sheet1")
    Set rng = .Range(.Cells(1, 1), .Cells(1, .Cells(1, .Columns.Count).End(xlToLeft).Column))
End With

 

[KNKN9]

Thank you this also works !

If the last column in row1 does not always have a value you could use

Sub KNKN9()
   Dim UsdCols As Long
   UsdCols = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
End Sub

 

[Fluff]

You're welcome & thanks for the feedback