How to skip hidden rows when pasting in Excel

Sometimes you need to copy and paste continuous row data from other areas into the filter area after filtering to replace the filtered data. Because the filtered range contains hidden rows, you cannot copy and paste directly, or some of the data is pasted into hidden rows. If you select the visible cells by using the "locate → visible cells" method after copying, and then press "CTRL + V" To paste, Excel prompts ... Excel cannot paste information because the copy region differs from the pasted region shape ... ". In this case, you can use a secondary column or VBA method to resolve it.

For example, as shown in the following figure, the columns A and B of the "Sheet1" table on the left contain some fruit names and quantities, and now you need to replace the "Sheet1" table with the number of "Apple" filtered by the values in the B1:b7 area of the right-hand "Sheet2" table (labeled Red).

Method one: Using secondary columns

This method uses the sort method to put the data that needs to be replaced together and then copy and paste. That is, first add an "ordinal" auxiliary column, enter the number ordinal, then use another auxiliary column to mark the filtered data, sort by the second secondary column, then copy and paste, and finally sort the order of the ordinal column to restore the original data. The specific steps are as follows:

1. First remove the filter, in the "Sheet1" table C2, C3 cells Enter the number "1", "2", select these two cells, double-click the fill handle, fill the ordinal number into column C.

2. Filter "Apple" in column A. Enter a formula in the cell in the second row of column D:

=row ()

Then fill the formula down to all visible cells in column D.

3. Remove the filter, sort by D, and arrange the number of "apples" together.

4. Copy the "Sheet2" table b1:b7 data to the B2:b8 area of the "Sheet1" table.

5. Sorted by ordinal column to restore the order of the original data.

Finally, delete the C, d two columns and complete the replacement.

Method Two: VBA code

Use the following VBA code to directly paste the data in the "Sheet2" Table B column into the filtered area of the Sheet1 table. If the data range for the "Sheet1" table is A1:b20, the Sheet2 Table B column needs to be copied and pasted to 7, filter "Apple" on "Sheet1" Table A, and then press ALT+F11 to open the VBA editor, enter the following code in the Code window and run it.

sub skip hidden rows while pasting ()
Dim Rng as range
Set Rng = Range ("B2:b20"). SpecialCells (xlcelltypevisible)
for each cell in Rng
Worksheets ("Sheet2"). Cells (i + 1, 2). Copy
Activesheet.paste destination:=cell
Application.cutcopymode = False
i = i + 1
If i = 7 Then end
N Ext
End Sub