Ignore Blank Cells When Pasting
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.
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.
- Copy the green cells
- Select the top left orange cell, where the data will be pasted
- On the Ribbon’s Home tab, click the arrow, below the Paste button
- Click Paste Special
- 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.
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.
Video: Ignore Blank Cells When Pasting
This very short video shows how to copy and paste, without overwriting the original data with blank cells.