Why won't the concatenate function in vba work?-CodePudding

I am new to vba and need a little help. I want to copy copy column A & C to sheet 2 but my concatenation syntax (my_range = rng1&":"&rng2) won't work.
I have tried other syntax too but it's just syntax to concatenate Strings into a single column and that's what I am looking for. What I want is Column A & C from sheet 1 to be copied in Column A & B in sheet 2.

Sub CommandButton1_Click()
Dim my_range As String, rng1 As String, rng2 As String

search_value = Sheets(2).Cells(i, 1).Value = 1

Sheets(1).Activate

For i = 2 To 100

If Sheets(1).Cells(i, 1).Value = search_value Then

rng1 = "A" & i
rng2 = "C" & i
my_range = rng1&":"&rng2

Sheets(1).Rande(my_range).Select
Selection.Copy
Sheets(2).Activate
Sheets(2).Range("A2").Select
Selection.PasteSpecial: xlPasteAll , SkipBlanks:=True, Transpose:=False

End If

Next
Application.CutCopyMode = False
Sheets(2).Cells(1, 2).Select

End Sub

CodePudding user response：

The simplest way to create a range from one cell to the other is the following:

my_range = Range(rng1, rng2)

(I found some examples on this website.)

CodePudding user response：

Do it like below :

rng1 = "A" & CStr(i)
rng2 = "C" & CStr(i)
my_range = rng1 & ":" & rng2