CODEWITHSUNDEEP
excelvba
MrT
MrT

Reputation: 61

What is the correct syntax for grouping rows

Syntax error using the following code. Both variables LR1 and LR2 were declared as integers:

sh.Rows("& LR1+1 &":"& LR2-1 &").Group 'Syntax error

No syntax error if hardcoded numbers are used:

sh.Rows("27:29").Group

Upvotes: 0

Views: 75

Answers (2)

Warcupine
Warcupine

Reputation: 4640

If your question is how strings and concatenating work:

The & forces string concatenation which means it will take strings or non-string types (but not all types like arrays) like your integer/long variables and combine them into a string.

In your code you have two strings and a character outside the string. "& LR1+1 &":"& LR2-1 &"

String 1: "& LR1+1 &" This is a valid string but Rows can't parse it.

Character: : This is causing the syntax error

String 2: "& LR2-1 &" This is a valid string but Rows can't parse it.

This is throwing a syntax error because : is not a valid character and isn't in a string.

an & needs to be outside the string otherwise it is just a part of the string. "&" will not concatenate.

What BigBen did in his comment is take your variables (which you have inside a string and as such are not variables they are just characters in a string that happen to look like your variables) and concatenate them into a string that Rows can work with.

So LR1 + 1 & ":" & LR2 - 1 Is taking the Value in LR1 and adding 1, then combining that number with ":", then subtracting 1 from LR2 and combining that into the string as well.

if LR1 = 10 and LR2 = 20 your final output will be:

"11:19"

You can check what your string will look like with debug.print

Upvotes: 4

BigBen
BigBen

Reputation: 49998

Variables don't belong inside quotes.

sh.Rows(LR + 1 & ":" & LR2 - 1)

Also, use Long instead of Integer.

Upvotes: 4

Related Questions