VBA code for selecting multi cells (that are apart/not continuous)

[ansvk1]

Hi there,
i am having trouble making my VBA code select the cells the way i wanted it to.

Here is what i wanted it to select

s2.Range("B2:M3,AB2:AB3").Select

at once to fill down the selection leaving the parts in middle. But my rowcount being dynamic, i have used variables and my code looks like

 s2.Range("B" & iLastRowS2 + 1 & ":M" & iLastRowS3, "AB" & iLastRowS2 + 1 & ":AB" & iLastRowS3).Select

this syntax is selecting everything from B2:AB3.

Now how can i tweak the syantax above to make use of my variables to select just the cells i wanted and not everything in between columns M and AB.

Any help is greatly appreciated, Thanks!

 

[kbrummert]

Did you try creating a range string outside the range statement? If it still isn't working, you can uncomment the msgbox to see if the syntax looks right....

dim rg_str as String


rg_str = "B" & iLastRowS2 + 1 & ":M" & iLastRowS3, "AB" & iLastRowS2 + 1 & ":AB" & iLastRowS3

' msgbox(rg_str)


s2.Range(rg_str).Select

 

[ansvk1]

no sir, i will try it now as you suggested and let you know. Thanks!

 

[ansvk1]

rg_str = "B" & iLastRowS2 + 1 & ":M" & iLastRowS3, "AB" & iLastRowS2 + 1 & ":AB" & iLastRowS3

Ok, i tried it and found 2 things: (1) i will not need a msgbox as long as it tests out correctly for its selection. (2) rg_str ="B".... code is giving me a syntax error.

Please advise, how to get past these roadblocks. Thanks!

 

[ansvk1]

Never mind, solved this problem with seperatly doing the selection one section after another. by selecting columns B:M once and then pasting the values for column AB using its dynamic values. Thanks for your Help!

 

[Rick Rothstein]

Here is what i wanted it to select

s2.Range("B2:M3,AB2:AB3").Select

at once to fill down the selection leaving the parts in middle. But my rowcount being dynamic, i have used variables and my code looks like

 s2.Range("B" & iLastRowS2 + 1 & ":M" & iLastRowS3, "AB" & iLastRowS2 + 1 & ":AB" & iLastRowS3).Select

this syntax is selecting everything from B2:AB3.

You were probably looking for this...

s2.Range("B" & iLastRowS2 + 1 & ":M" & iLastRowS3 & ",AB" & iLastRowS2 + 1 & ":AB" & iLastRowS3).Select