I prefer to use Range Shortcut [ ] instead of Range(" ").

But I cannot concatenate variable to it.

ActiveSheet.Range("A2:A" & LastRow).Select    ‘This works

ActiveSheet.[A2:A & LastRow].Select ‘not works

I got error Object required.

CodePudding user response：

You cannot add variables within the brackets. That's why it's returning the object required error.

ExcelHero did tests and found that the brackets run slower than when range is clearly defined in the code: http://www.excelhero.com/blog/2010/06/when-working-in-vba-we.html

CodePudding user response：

Use Evaluate to do what you want to do

ActiveSheet.Evaluate("A2:A" & lastRow).Select

Using square brackets (for example, "[A1:C5]") is identical to calling the Evaluate method with a string argument.

Further reading

refer-to-cells-by-using-shortcut-notation

excel.application.evaluate

PS As the OP is after having a string variable in the expression with [] and as I do not think that this is possible another quote from the documentation to support my point of view.

The advantage of using square brackets is that the code is shorter. The advantage of using Evaluate is that the argument is a string, so you can either construct the string in your code or use a Visual Basic variable.