How to define a variable string range in excel

Question

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"

something like sheet("test").range("a1").end(xltoright).formula="xyz" should do it I think, last column would be an integer. or maybe cells(sheet("test").range("a1").end(xltoRight),1).formula — Nathan_Sav
– Nathan_Sav, Commented Sep 30, 2016 at 14:00

Scott Craner · Accepted Answer · 2016-09-30 14:23:44Z

4

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"

edited Sep 30, 2016 at 14:23

answered Sep 30, 2016 at 14:01

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

John Bustos Over a year ago

Beat me to it - Nice answer! - I'd just maybe change lastcolumn to an integer.

user4039065 Over a year ago

Why not declare and treat lastcolumn as the integer that it is? (btw, it is Sheets, not Sheet).

Scott Craner Over a year ago

@Jeeped got tied up in the other part missed that it was a string not a Integer/Long.

Scott Craner Over a year ago

@Jeeped that's what i get by trying to be like you and just correct the code directly in the answer space and not through VBE.

user4039065 Over a year ago

LOL - Almost every time I try to correct code without the VBE I get bitten in the a$$! You are not following my posts' edit history of corrections! (btw, big day tomorrow... fingers crossed)

|

user3598756 · Accepted Answer · 2016-10-01 09:34:22Z

0

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

answered Oct 1, 2016 at 9:34

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to define a variable string range in excel

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related