CODEWITHSUNDEEP
excelvba
JoaMika
JoaMika

Reputation: 1827

Populate Variable with all values in Row

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

Upvotes: 0

Views: 74

Answers (2)

L42
L42

Reputation: 19737

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.

Upvotes: 1

AnalystCave.com
AnalystCave.com

Reputation: 4984

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...

Upvotes: 0

Related Questions