使用复制和粘贴VBA避免选择

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!

我试图减少我对select的使用。我一直在参考下面的文章,但我不完全确定如何在这种情况下实现它。为了练习,我在下面使用了一组工作代码,用于复制B1中的值并将其粘贴到A列中填充的多行。如果有人可以指导我如何避免这种不良习惯以便写更多高效而有效的代码,我将不胜感激!

How to avoid using Select in Excel VBA

如何避免在Excel VBA中使用Select

    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

3 个解决方案

#1

All of that code can be translated to this:

所有代码都可以转换为:

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

范围(“B1:B”和单元格(Rows.Count,“A”)。结束(xlUp).Row).Value =范围(“B1”)。值

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.

我们正在制作单元格范围B1到B以及A列中的最后一行单元格(我们通过使用单元格(Rows.Count,“A”)获得.End(xlUp).Row)等于单元格中的值B1。

#2

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.

是说存储对范围的引用:B1。

Selection.Copy

is saying use that reference, and call the function Copy

正在说使用那个引用,并调用函数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.

偏移函数只是在指定的范围内指定Start,并移动指定数量的列或行,并使用此单元格作为参考。

#3

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

#1