Stupid Question About Copying Rows

[beartooth91]

Hello Everyone -

New to the forum, but have been writing VBA for several years.

I have what is likely a stupid question in regards to copying rows.....

See the two code snips below. They do not do the same thing and I don't understand why.

Specifically, the two range statements are different, but I can't figure out why they act different. May I ask someone here to explain?

Thanks


Dim c As Long

c = Workbooks("Master Database.xlsm").Worksheets("Inputs").Range("B" & Rows.Count).End(xlUp).Row
'
'Copy Point Name
'Workbooks("Master Database.xlsm").Worksheets("Inputs").Range("B11" & c).Copy '<-----doesn't work, copies a blank cell

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

'Copy Point Name

Workbooks("Master Database.xlsm").Worksheets("Inputs").Activate

Workbooks("Master Database.xlsm").Worksheets("Inputs").Range("B11", Range("B" & Rows.Count).End(xlUp)).Copy

 

[MARK858]

In the 1st code, if the last row was row 1000 you are writing

Range("B111000")

it should be

Range("B11:B" & c)

 

[beartooth91]

In the 1st code, if the last row was row 1000 you are writing

Range("B111000")

it should be

Range("B11:B" & c)

Yeah, I don't get it. To me, both look like they should be selecting the same cell/range. The top piece selects "B823" and the bottom VBA selects "B8:B23".

 

[MARK858]

both look like they should be selecting the same cell/range.

No in your first code you are concatenating the last row number to B11 and so if the last row was 9999 you are writing B119999 not B11:B9999.... it needs to be B11:B not just B11


Edit:
In your last example you are concatenating 23 to B8 so you get B823 not B8:B23

 

[beartooth91]

No in your first code you are concatenating the last row number to B11 and so if the last row was 9999 you are writing B119999 not B11:B9999.... it needs to be B11:B not just B11


Edit:
In your last example you are concatenating 23 to B8 so you get B823 not B8:B23

Uh-Oh....
So, I've been thinking this entire time that the '&' operator adds the two. You're saying it joins the range and last row integer, end to end......(?)

 

[MARK858]

You're saying it joins the range and last row integer, end to end......(?)

Yes, it is an AND not a PLUS

Edit:

Ampersand (&) operator

Office VBA reference topic

learn.microsoft.com

VBA Concatenate Strings with Ampersand Operator - wellsr.com

In VBA, you concatenate strings into a single string using the &, or ampersand, operator. Simply add an ampersand between your strings to combine them into one string.

wellsr.com