- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Paste Values to Visible/Filtered Cells Only in Excel?
You may frequently find yourself in circumstances where you want to paste certain values without affecting the hidden or filtered cells when working with huge datasets or applying filters to your data. Fortunately, Excel has a straightforward yet effective capability to carry out this activity.
This article will walk you through the step-by-step process of pasting values to cells that are visible or that have been filtered only. By doing this, you may update or modify your data while keeping the integrity of your data intact. Regardless of your level of Excel proficiency, this book will help you master the method and increase your productivity.
Paste Values to Visible/Filtered Cells Only
Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to know how you can paste values to visible or filtered cells only in Excel.
Consider an Excel sheet where the data in the sheet is similar to the below image.
First, right-click on the sheet name and select View code to open the VBA application.
Right Click > View Code.
Then click on Insert and select Module, then copy the below code into the text box.
Insert > Module > Copy.
Sub CopyFilteredCells() Dim rng1 As Range Dim rng2 As Range Dim InputRng As Range Dim OutRng As Range xTitleId = "Paste To Visible" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Copy Range :", xTitleId, InputRng.Address, Type: = 8) Set OutRng = Application.InputBox("Paste Range:", xTitleId, Type: = 8) For Each rng1 In InputRng rng1.Copy For Each rng2 In OutRng If rng2.EntireRow.RowHeight > 0 Then rng2.PasteSpecial Set OutRng = rng2.Offset(1).Resize(OutRng.Rows.Count) Exit For End If Next Next Application.CutCopyMode = False End Sub
Then click F5 to run the module, select the range of cells you want to copy, and click OK.
F5 > Select Cells > Ok.
Then select the range of cells to paste and click OK to complete the task.
Select Cells > Ok.
This is how you can paste values into visible or filtered cells only in Excel.
In this tutorial, we have used a simple example to demonstrate how you can paste values into visible or filtered cells only in Excel to highlight a particular set of data.
Kickstart Your Career
Get certified by completing the courseGet Started