Pasting Data on Filtered Rows
When we paste data on filtered rows it always past it on both visible and hidden rows. And if you are doing this for a huge set of data then I am sure you must be pasting it manually and wasting lot of time and efforts. Don’t worry guys, we have a solution for this but only through VBA.
So let’s see an example, below is a screenshot of a filtered data:
When you simply use Copy and paste it looks like this:
So it will get pasted on hidden rows also, to avoid the same, please follow the below steps :
- Press Alt + F11 keys simultaneously, and a Microsoft Visual Basic for Applications (VBA) window will open.
- Click Insert > Module, then paste below VBA code to the Module window.
Sub PasteOnVisibleCells()
Dim rg1 As Range
Dim rg2 As Range
Dim CopyRg As Range
Dim PasteRg As Range
Set CopyRg = Application.Selection
Set CopyRg = Application.InputBox(“Copy Range :”, , CopyRg.Address, Type:=8)
Set PasteRg = Application.InputBox(“Paste Range:”, , Type:=8)
For Each rg1 In CopyRg
rg1.Copy
For Each rg2 In PasteRg
If rg2.EntireRow.RowHeight > 0 Then
rg2.PasteSpecial
Set PasteRg = rg2.Offset(1).Resize(PasteRg.Rows.Count)
Exit For
End If
Next
Next
Application.CutCopyMode = False
End Sub
- Click F5 key or the Run button, a dialog box will appear for you to select Range to copy
4. After selecting the range, click OK, and then another dialog box will appear for you to select a range to paste the copied data in.
5. Click OK to get your desired result.
Download the same file here.
570 total views, 3 views today
Tag:Advanced Excel, Excel, Filter, Filtered Data, VBA