Populate Variable with all values in Row

Question

I am trying to create a variable with all the cell values in used columns in Row 1.

Dim vaData As Variant

With wsSheet
vaData = .Range("B1:" & .Range(.Columns.Count & "1").End(xlRight).Column).Value
End With

I am getting a range failed error.

I have successfully done this for getting values in a column, BK.

vaData = .Range(.Range("BK2"), .Range("BK100").End(xlUp)).Value

Answer 1

Try this:

Dim vaData As Variant

With wsSheet
    vaData = .Range("B1", .Cells(1, .Columns.Count).End(xlToLeft))
End With

The thing is you might have been confused between Range Object and Cells Property.
Refer to below Range Object Syntax I frequently use:

• Range("Cell1", "Cell2") e.g. Range("A1", "A10")
• Range("Cell1:Cell2") e.g. Range("A1:A10")
• Range(Cells(1),Cells(2)) e.g. Range(Cells(1, 1), Cells(10, 1))

Above all evaluates to Range A1:A10. Now how to make it dynamic?

Examples:

Dynamic Last Row

With Sheet1
    .Range("A1", .Range("A" & .Rows.Count).End(xlUp)) '~~> dynamic last row
    .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row) '~~> same as above
    .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) '~~> same as above
End With

Dynamic Last Column

With Sheet1
    .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft)) '~~> same as my answer
    .Range(.Cells(1, 1), .Cells(1, .Columns.Count).End(xlToLeft)) '~~> same as above
End With

Btw, another more complex approach is found here using Find Method.
The same approach was used here to find the last column.
Also you might want to check different ways on using Cells Property.

Answer 2

My first guess is that in the first example you are missing the column letter i.e. the formula will evaluate to something like

vaData =.Range("B1:10").Value 'Error!

Instead of

vaData =.Range("B1:B10").Value 'Ok!

Hope this helps...