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

Answer 1

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.

Answer 2

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.