2

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
Improve this question
4
  • 4
    This question is asked almost every day now and we have tons on SO! Commented Jul 18, 2018 at 15:35
  • What to do when duplicate question is asked meta.stackexchange.com/questions/10841/… 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 Commented Jul 18, 2018 at 15:39
  • @TDJ: also, take a look at MSDN: Excel VBA reference and Chip Pearson's excellent site Commented Jul 18, 2018 at 15:41

3 Answers 3

Reset to default
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.

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

1 Comment

Thank you, this is very helpful.
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.

img1

Improve this answer

3 Comments

Maybe next time! ;)
OP code will stop if there is an empty cell in column A.
@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.
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
Improve this answer

2 Comments

Nope. OP wants the value in B1 down, not the values in column A.
Yeah you are right, I read the question wrong. Looks like dwirony already got the answer for him.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.