A very common situation, a question about which I am asked almost at every training. There is a table in which the Filter (Data – Filter) several lines are selected. The task is to insert any values we need exactly into the visible filtered rows, while skipping the hidden ones. Normal copy-paste will not work in this case, because data will be inserted not only into visible, but also into hidden cells. Let’s see how we can get around this problem.
Method 1: Paste duplicate values or formulas
If you need to insert the same values into all the filtered rows of a list, then everything is simple. Suppose we have the following list of transactions:
… and in it you need to put a fixed discount of 1000 rubles for each Auchan.
We filter our list with AutoFilter, leaving only “Auchans” on the screen. Enter the desired value in the first cell and stretch (copy from the lower right corner of the cell) down:
Smart Excel in this case understands that you want to enter values in the filtered cells and does what you need:
This method is suitable for entering values and for entering formulas. For example, if the discount for Auchan is not fixed, but is 10% of the transaction amount, then in the first filtered line you can enter not a constant (1000), but a formula (=C2*10%) and also copy it down.
Method 2. Macro to insert any values
Another thing is if you need to insert into the filtered cells not the same values or formulas, but different ones, and even take them from a different range. Then you have to use a simple macro. Press keyboard shortcut Alt + F11, in the Visual Basic window that opens, insert a new empty module through the menu Insert – Module and copy this code there:
Sub PasteToVisible() Dim copyrng As Range, pasterng As Range Dim cell As Range, i As Long 'prompt the user for copy and paste ranges in turn Set copyrng = Application.InputBox("Copy range", "Request", Type:=8 ) Set pasterng = Application.InputBox("Insert Range", "Request", Type:=8) 'check that they are the same size If pasterng.SpecialCells(xlCellTypeVisible).Cells.Count <> copyrng.Cells.Count Then MsgBox "Copy and paste ranges of different sizes!",vbCritical Exit Sub End If 'transfer data from one range to another only in visible cells i = 1 For Each cell In pasterng If cell.EntireRow.Hidden = False Then cell.Value = copyrng. Cells(i).Value i = i + 1 End If Next cell End Sub As you can easily imagine, the macro prompts the user for two ranges in turn – copy and paste. Then it checks that their sizes are the same, because the difference in dimensions will subsequently cause an insertion error. The macro then loops through all the cells in the paste range and transfers the data from the copy range there if the row is visible (i.e. not filtered).
- What are macros, how to use them, where to insert macro code in Visual Basic