Blog

How To Replace Parts Of String In VBA

Dim creamResult As String

creamResult = Replace(“alphabet”, “a”, “e”)

 

Exampe 2 (With sample code):

Selecting Ranges by variables*

 

I recorded a macro with ‘Relative References’ and this is what I got :

Range("F10").Select
ActiveCell.Offset(0, 3).Range("A1:D11").Select

Heres what I thought : If the range selection is in quotes, VBA really wants a STRING and interprets the cells out of it so tried the following:

Dim MyRange as String
MyRange = "A1:D11"
Range(MyRange).Select

And it worked 🙂 ie.. just create a string using your variables, make sure to dimension it as a STRING variables and Excel will read right off of it 😉

Following tested and found working :

Sub Macro04()

Dim Copyrange As String

Startrow = 1
Lastrow = 11
Let Copyrange = "A" & Startrow & ":" & "D" & Lastrow
Range(Copyrange).Select
End Sub

 

References:

https://stackoverflow.com/questions/12199318/vba-selecting-range-by-variables