With statement not working unless sheet is active

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Mar 13, 2017

• #2

When using syntax like
Range(Range(...), Range(...))

And you qualify the sheet, you must qualify the sheet on all 3 parts of the range.
Sheets(...).Range(Sheets(...).Range(...), Sheets(...).Range(...))

 

Upvote 0

S

snjpverma

Well-known Member

Joined

Oct 2, 2008

Messages

1,584

Office Version

1. 365

Platform

1. Windows

• Mar 13, 2017

• #3

Since I am a beginner in vba, i would like to know what is a easy alternative to that.

 

Upvote 0

Logit

Well-known Member

Joined

Aug 31, 2016

Messages

4,846

• Mar 13, 2017

• #4

I'm using Excel 2007 that provides the maximum of 1048576 rows.
Your macro is asking Excel to copy all 1048576 rows, then move all of them down 5 rows and paste.

In essence you are wanting Excel to create 5 new additional rows that it doesn't have.

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Mar 13, 2017

• #5

snjpverma said:

Since I am a beginner in vba, i would like to know what is a easy alternative to that.

Click to expand...

The With structure is a nice alternative, as the OP was trying to do.
But needed to add the . to both ranges inside the original range.

Code:

With Worksheets("Sub-Client Config")
   [COLOR=#FF0000][B] .[/B][/COLOR]range(range("A1"), range("A1").Offset(0, 2).End(xlDown)).Copy
End With

Should be

Code:

With Worksheets("Sub-Client Config")
   [COLOR=#FF0000][B] .[/B][/COLOR]range([B][COLOR=#ff0000].[/COLOR][/B]range("A1"), [B][COLOR=#ff0000].[/COLOR][/B]range("A1").Offset(0, 2).End(xlDown)).Copy
End With

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Mar 13, 2017

• #6

Logit said:

Your macro is asking Excel to copy all 1048576 rows

Click to expand...

Only if there is no data in column C
If there is data populated in C1 and below, then the end(xldown) will only go to the last cell before an empty cell.
But if there is no data below C1, then yes, it goes all the way to the end.

 

Upvote 0

D

DanGK1

Board Regular

Joined

Jul 7, 2010

Messages

60

• Mar 13, 2017

• #7

Thanks Jonmo and thanks Logit for the observation - I need to add an 'if' row count = 1 to the code to avoid the whole of column C being selected

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Mar 13, 2017

• #8

You're welcome.

Alot of people prefer to use LastRow and end(xlUP) to deal with this issue.


That would be like this
.range(.range("A1"), .range("A" & Rows.Count).Offset(0, 2).End(xlUp)).Copy


Keep in mind, it's not quite exactly the same.

If you were intentionally using row 1 DOWN because you have blanks mixed in and only want to get data down to the first blank,
Then you're original is more appropriate.

Using LastRow UP goes to the very last (furthest down) non blank cell in the column.

 

Upvote 0

Logit

Well-known Member

Joined

Aug 31, 2016

Messages

4,846

• Mar 13, 2017

• #9

Please post your final working macro so others will benefit in the future.

Thanks.

 

Upvote 0

S

snjpverma

Well-known Member

Joined

Oct 2, 2008

Messages

1,584

Office Version

1. 365

Platform

1. Windows

• Mar 13, 2017

• #10

thanks for the info Jomno, I now understand that this .range(.range(A1") format is used when the last row is not defined.
i think defining the lastrow earlier simplifies the code. am I correct ?

 

Upvote 0