CODEWITHSUNDEEP
excelvba
Chewy1985
Chewy1985

Reputation: 35

How to define a variable string range in excel

I need to count the number of columns within a variable range and use the address of the last column to form part of defined range. Once I have defined the range I will want to input a formula. Can anyone please help?

Example

Dim lastcolumn as string

Lastcolumn = sheet("test").range("a1").end(xltoright).column

Sheets("test").range("b1:" & lastcolumn).select

Selection.formula = "myformula"

Upvotes: 0

Views: 331

Answers (2)

user3598756
user3598756

Reputation: 29421

you can get rid of lastColumn calculation (and bothering about its type...)

With Sheets("test")
    .Range("B1", .Range("A1").End(xlToRight)).Formula = "myformula"
End With

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

The column returns a number not a cell so you need to provide more info. You will need to use Cells().

Also avoid using Select it slows down the code:

Dim lastcolumn as Long

Lastcolumn = sheets("test").range("a1").end(xltoright).column

Sheets("test").range("b1",Sheets("test").Cells(1,lastcolumn)).formula ="myformula"

Upvotes: 4

Related Questions