Avoiding Select with Copy and Paste VBA

Question

I am trying to reduce my usage of select. I have been referencing the article below but I am not entirely sure how to implement it in this scenario. To practice I have the working set of code below that I use to copy the value in B1 and paste it down as many rows that are filled in column A. If someone could coach me through how to avoid this bad habit in order to write more effective and efficient code, I would greatly appreciate it!

How to avoid using Select in Excel VBA

    Range("B1").Select
    Selection.Copy
    ActiveCell.Offset(0, -1).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range(Selection, "B1").Select
    ActiveSheet.Paste

This question is asked almost every day now and we have tons on SO! — Santosh
– Santosh, Commented Jul 18, 2018 at 15:35
What to do when duplicate question is asked meta.stackexchange.com/questions/10841/… — Santosh
– Santosh, Commented Jul 18, 2018 at 15:38
Not sure this question is for SO as OP doesn't have an issue with his code, they just want to know a better way to do this. Possible for one of the sister sites where code is reviewed — Zac
– Zac, Commented Jul 18, 2018 at 15:39
@TDJ: also, take a look at MSDN: Excel VBA reference and Chip Pearson's excellent site — Our Man in Bananas
– Our Man in Bananas, Commented Jul 18, 2018 at 15:41

JosephC · Accepted Answer · 2018-07-18 15:51:13Z

6

Others have provided direct solutions to your code. But to your question on being "Coached" on how to avoid Select, this is what is happening...

Consider .Select as storing a Reference to whatever it's attached to.

Range("B1").Select

is saying store a reference to the Range: B1.

Selection.Copy

is saying use that reference, and call the function Copy

Instead of using the reference, you can access it directly.

Range("B1").Copy

Now since your copying and pasting you have two different references, your source and your destination. So you can apply the value directly from source to destination, without invoking copy and paste functions.

Range(<DestinationRange>).value = Range(<SourceRange>).value

The offset functions are simply saying Start at the Range specified, and move over the specified number of column(s) or row(s), and use this cell as your reference.

answered Jul 18, 2018 at 15:51

JosephC

9294 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TDJ Over a year ago

Thank you, this is very helpful.

dwirony · Accepted Answer · 2018-07-18 15:33:38Z

4

All of that code can be translated to this:

Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row).Value = Range("B1").Value

We're making the Range of cells B1 through B and the last row of cells in column A (which we get by using Cells(Rows.Count, "A").End(xlUp).Row) equal to the value in cell B1.

edited Jul 18, 2018 at 15:33

answered Jul 18, 2018 at 15:31

dwirony

5,4604 gold badges26 silver badges49 bronze badges

3 Comments

dwirony Over a year ago

Maybe next time! ;)

Vincent G Over a year ago

OP code will stop if there is an empty cell in column A.

dwirony Over a year ago

@VincentG From the sounds of it he doesn't have empty cells in column A, but according to his question he wanted to "paste it down as many rows that are filled in column A", which sounds like the typical fill down to last row situation.

Hasib_Ibradzic · Accepted Answer · 2018-07-18 15:34:53Z

0

You could do this:

Sub copyPaste()
Dim lastRow As Long

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

Sheets("Sheet1").Range("B1:B" & lastRow).Value = Sheets("Sheet1").Range("A1:A" & lastRow).Value
End Sub

edited Jul 18, 2018 at 15:34

answered Jul 18, 2018 at 15:33

Hasib_Ibradzic

6665 silver badges23 bronze badges

2 Comments

BigBen Over a year ago

Nope. OP wants the value in B1 down, not the values in column A.

Hasib_Ibradzic Over a year ago

Yeah you are right, I read the question wrong. Looks like dwirony already got the answer for him.

Collectives™ on Stack Overflow

Avoiding Select with Copy and Paste VBA

3 Answers 3

1 Comment

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related