vba拷贝粘贴单元格_粘贴时忽略空白单元格

vba拷贝粘贴单元格

Last week, I was working on a client's file, and we wanted to get any new information from an update file, and paste it into the current record.

上周，我正在处理客户文件，我们希望从更新文件中获取任何新信息，并将其粘贴到当前记录中。

Both files were set up with the same columns, but the update file only had a few of the fields filled in. We could have used a macro to loop through the fields, grab any new information, and paste it to the current record. However, that would be a slow way to update the records, especially in a large file.

两个文件都设置有相同的列，但更新文件仅填充了一些字段。我们可以使用宏在这些字段之间循环，获取任何新信息并将其粘贴到当前记录中。 但是，这将是更新记录的缓慢方法，尤其是在大文件中。

Fortunately, there is a built-in feature in Excel, that will copy a block of cells, and only paste the cells that contain data.

幸运的是，Excel中有一个内置功能，它将复制一个单元格块，并且仅粘贴包含数据的单元格。

新旧数据 (The Old and New Data)

In the screen shot below, you can see data that is similar to the two files that I was using in the macro. At the top, you can see the current data, and the orange rows are going to be updated.

在下面的屏幕快照中，您可以看到与我在宏中使用的两个文件相似的数据。 在顶部，您可以看到当前数据，橙色行将被更新。

The Update file is shown at the bottom, and the green cells have some new data. I've circled a few of the places where there are blanks cells in the Update file, and valid data in the current record. We don't want to lose that data, when pasting in the new information.

更新文件显示在底部，绿色单元格包含一些新数据。 我已经圈出了更新文件中空白单元格和当前记录中有效数据的一些位置。 粘贴新信息时，我们不想丢失这些数据。

使用粘贴特殊命令 (Use the Paste Special Command)

To paste the new data, without overwriting the existing data with blank cells, you can use the Paste Special command.

要粘贴新数据而不用空白单元格覆盖现有数据，可以使用“选择性粘贴”命令。

1. Copy the green cells
   复制绿色单元格
2. Select the top left orange cell, where the data will be pasted
   选择左上角的橙色单元格，将数据粘贴到该单元格中
3. On the Ribbon's Home tab, click the arrow, below the Paste button
   在功能区的“主页”选项卡上，单击“粘贴”按钮下方的箭头。
4. Click Paste Special
   单击特殊粘贴
5. In the Paste Special window, add a check mark to "Skip Blanks", then click OK
   在“选择性粘贴”窗口中，向“跳过空白”添加一个复选标记，然后单击“确定”。

The data from the Update records will be pasted into the current record, but the blank cells won't overwrite the cells with valid data, in the current record.

Update记录中的数据将粘贴到当前记录中，但是空白单元格不会在当前记录中覆盖有效数据单元格。

To use this technique in a macro, turn on the macro recorder, while you follow the steps listed above. Then, turn off the recorder, and incorporate the code into your existing macro.

要在宏中使用此技术，请按照上面列出的步骤打开宏记录器。 然后，关闭记录器，并将代码合并到您现有的宏中。

下载样本文件 (Download the Sample File)

To see how this feature works, you can download the sample file from the Data Entry Tips page on my Contextures website. The zipped file is in xlsx format, and does not contain macros.

若要查看此功能的工作原理，可以从Contextures网站上的“数据输入提示”页面下载示例文件 。 压缩文件为xlsx格式，不包含宏。

视频：粘贴时忽略空白单元格 (Video: Ignore Blank Cells When Pasting)

This very short video shows how to copy and paste, without overwriting the original data with blank cells.

这个非常短的视频展示了如何复制和粘贴而不用空白单元格覆盖原始数据。

演示地址

翻译自: https://contexturesblog.com/archives/2014/11/20/ignore-blank-cells-when-pasting/

vba拷贝粘贴单元格