selecting a range with an unknown col end

KNKN9

Board Regular
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

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

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

Fluff

MrExcel MVP, Moderator
If the last column in row1 does not always have a value you could use
Code:
Sub KNKN9()
   Dim UsdCols As Long
   UsdCols = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
End Sub
 

KNKN9

Board Regular
This works exactly what I am after !
Thank you !
See if this is what you mean? The range is assigned to the variable rng.

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

KNKN9

Board Regular
Thank you this also works !
If the last column in row1 does not always have a value you could use
Code:
Sub KNKN9()
   Dim UsdCols As Long
   UsdCols = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
End Sub
 

Some videos you may like

This Week's Hot Topics

Top