Proper VBA code in Excel - Selecting cells

Posted on 2013-10-04
Medium Priority
Last Modified: 2013-10-11
I am trying to figure out how to copy a dynamic range on another sheet to paste into the current sheet.  The range would be cell A1 and extended to the right.  I can make it happen with Select statements, but that's not the right way.  Just trying to figure out the "best" way without Select.

This is the code that works:
    Range(ActiveCell, ActiveCell.End(xlToRight)).Copy

Open in new window

This does not (I get  an Object Required error on the second line):
    Locat = Sheets(2).Range("a1").Address
    Range(Locat, Locat.End(xlToRight)).Copy

Open in new window

This also does not work -I get a Compile error Expected: List seperator or ) and it highlights the period beforee end
Sheets(2).range("a1", "a1".End(xlToRight)).Copy

I know I am just doing one thing wrong, but can't find it.  Thanks in advance.
Question by:pmpatane
LVL 39

Assisted Solution

nutsch earned 600 total points
ID: 39547088
Try this:

with Sheets(2)
.range(.cells(1,"A"), .cells(1,"A").End(xlToRight)).Copy
'or .range(.cells(1,1), .cells(1,1).End(xlToRight)).Copy
'or Sheets(2).range(.[a1], .[a1].End(xlToRight)).Copy
end with

Open in new window

LVL 10

Accepted Solution

FamousMortimer earned 600 total points
ID: 39547173

Sheets(2).Range("A1:A" & Sheets(2).Range("A1").End(xlToRight).Row).Copy

Author Comment

ID: 39548660
Thanks FamousMortimer...I really like that solution as well...more in line with my thinking when I code
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 14

Expert Comment

ID: 39550266
Change your way of thinking.
You asked for the "best" way. Look at nutsch' line 3.

Remember that Excel is good at numbers. Therefore it gives numbers to its rows and columns, not letters. Cell(1, 1) requires less translation that Cells(1, "A") which requires less translation that Range("A1").
From the viewpoint of efficiency Range("A1:A" & Sheets(2).Range("A1").End(xlToRight).Row) definitely is worse than any of the above.

If you want fast and efficient code use the syntax least taxing for Excel's resources.
LVL 10

Expert Comment

ID: 39552101
You are really splitting hairs.  This is one operation, not billions.  Although a nano second or two might be saved, it is easier to read the code so at that point it becomes the preference of the programmer.

Author Comment

ID: 39552738
@FamousMortimer, I made one slight change as I am copying A1 to the right
Sheets(2).Range("A1:" & Sheets(2).Range("A1").End(xlToRight).Address).Copy

Open in new window

@Faustulus, I understand what you're saying about Excel liking numbers and having to "convert" numbers to letters (e.g. the COLUMN function, etc.) but this code is from 8 lines of code and it is just combining the data from all sheets into one without doing any calculations on the data.  Lots of copying and pasting and I was making sure to avoid Select statements as those are ridiculous time wasters.

Thank you both for your input (and nutsch as well)!  I like learning and seeing many different ways to accomplish the same goal

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question