Range separator issue

[Nightlytic]

Alright,

So I have a macro that moves big chunks of data into a single worksheet using .copy and .paste on the ranges, I want to change this to use the = instead

My issue here sound rather stupid, you know the basic:
Sheets("Sheet1").Range("A1:B10")=Sheets("Sheet2").Range("A1:B10")
And then if you don't know where to end, you can do
LastRow = 10
Sheets("Sheet1").Range("A1:B"&LastRow)=Sheets("Sheet2").Range("A1:B"&LastRow)
But what if I need to have a variable on both the first and the last rows? it's telling me the separator is wrong if I do
Sheets("Sheet1").Range("A"&FirstRow&":"&"B"&LastRow)=Sheets("Sheet2").Range("A"&FirstRow&":"&"B"&LastRow)

Or whatever I can come up with. I'll attach a file, the goal ultimately is to plop data (which has no headings) in sheet 2, on bottom of sheet 1 data, and use = as the method as copy and paste I found didn't work for my particular project. Im trying to find the most performance efficient way as there are a lot of files to merge, running on a loop.

Please help? :/

 

[NARAYANK991]

Hi ,

There is no problem ; the following works :

x.Activate
ActiveSheet.Range("A2:A" & LastRow2).Select
x.Activate
ActiveSheet.Range("A" & LastRow1 & ":" & "A" & LastRow2).Select

Narayan

 

[Marc L]

Hi !

Nightlytic, VBA just requires a space before and after & operator !

An easy way :​

Sub Demo1()
    VA = Sheet2.[A1].CurrentRegion.Value
    Sheet1.Cells(Rows.Count, 1).End(xlUp)(2).Resize(UBound(VA), UBound(VA, 2)).Value = VA
End Sub

Do you like it ? So thanks to click on bottom right Like !

Wanna to understand ? As all is yet in VBA inner help, just open it …
​

 

[Nightlytic]

Hi ,

There is no problem ; the following works :

x.Activate
ActiveSheet.Range("A2:A" & LastRow2).Select
x.Activate
ActiveSheet.Range("A" & LastRow1 & ":" & "A" & LastRow2).Select

Narayan

That does work Sorry I have absolutely no idea where this was wrong I kept getting separator errors. Need a rubber duck on my desk STAT.

Thank you Narayan Appreciate it

 

[Marc L]

Same easy way but without any variable :​

Sub Demo2()
    With Sheet2.[A1].CurrentRegion
         Sheet1.Cells(Rows.Count, 1).End(xlUp)(2).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End Sub

You may Like it !

Think, But Think Object !
​

 

[Nightlytic]

Hi !

Nightlytic, VBA just requires a space before and after & operator !

An easy way :​

Sub Demo1()
    VA = Sheet2.[A1].CurrentRegion.Value
    Sheet1.Cells(Rows.Count, 1).End(xlUp)(2).Resize(UBound(VA), UBound(VA, 2)).Value = VA
End Sub

Do you like it ? So thanks to click on bottom right Like !

Wanna to understand ? As all is yet in VBA inner help, just open it …
​

Oh is that the culprit? Huh... Thank you Marc!

 

[NARAYANK991]

Hi ,

The reason is slightly different.

Suppose you have a statement such as :

x.Range("A" & LastRow1 & ":" & "A" & LastRow2).Value = y.Range("A" & LastRow1 & ":" & "A" & LastRow2).Value

You can try deleting the space between "A" and & ; Excel will automatically reinsert the space character ; try deleting the space between the & and LastRow1; Excel will automatically reinsert the space character.

The issue comes in only in the space between LastRow1 and & !

This is because LastRow1& (together , without a space in between) is an implicit type declaration of the variable LastRow1 as of type Long.

Thus , when Excel encounters LastRow1& ":" , with the & as a part of the VBA variable LastRow1 , it generates an error.

Except for this location , everywhere else , not using a space before or after the & will not create a problem , since Excel will automatically insert the required space character.

Narayan

 

[Nightlytic]

Yeah, I grew lazy because it formats my code. But I see, so I essentially referred to a range LastRow1& that didn't exist, alright. Thanks

 

[p45cal]

if you're going to hard-code:
& ":" & "A" &
then do it thus:
& ":A" &