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, Jul 18 '18 at 15:35
What to do when duplicate question is asked https://meta.stackexchange.com/questions/10841/how-should-duplicate-questions-be-handled — Santosh, Jul 18 '18 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, Jul 18 '18 at 15:39
@TDJ: also, take a look at [MSDN: Excel VBA reference](https://msdn.microsoft.com/en-us/vba/vba-excel) and [Chip Pearson's excellent site](http://www.cpearson.com/Excel/Topic.aspx) — Our Man in Bananas, Jul 18 '18 at 15:41

score 5 · Answer 1 · answered Jul 18 '18 at 15:51

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.

Thank you, this is very helpful. – TDJ Jul 18 '18 at 15:55 — TDJ, Jul 18 '18 at 15:55

dwirony · Accepted Answer · 2018-07-18T15:33:38.027

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 '18 at 15:33

answered Jul 18 '18 at 15:31

dwirony

5,487
3
21
43

Maybe next time! ;) – dwirony Jul 18 '18 at 15:34
OP code will stop if there is an empty cell in column A. – Vincent G Jul 18 '18 at 15:45
@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. – dwirony Jul 18 '18 at 15:49

Hasib_Ibradzic · Answer 3 · 2018-07-18T15:34:53.377

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 '18 at 15:34

answered Jul 18 '18 at 15:33

Hasib_Ibradzic

666
5
23

Nope. OP wants the value in B1 down, not the values in column A. – BigBen Jul 18 '18 at 15:34
Yeah you are right, I read the question wrong. Looks like dwirony already got the answer for him. – Hasib_Ibradzic Jul 18 '18 at 15:36

Avoiding Select with Copy and Paste VBA

3 Answers3

Linked