Md. Abdul Kader
Often you may need to copy filtered rows in Excel. And this task can be accomplished using Excel as well as the VBA code. In this article, I’ll show you 5 methods on how to copy rows in Excel with a filter with proper explanation.
Download Practice Workbook
6 Methods to Copy Rows in Excel with Filter
Let’s be introduced with today’s dataset (B1:F16 cell range) as shown in the following screenshot. Here, the Number of Visits for each website is provided along with the Name and Category of the Sites. Besides, the dates and mode of Platforms are also given. Now, we need to copy rows keeping filters.
Before going to the methods, let me ask you a question.
Do you know how to apply filter in Excel?
Truly speaking, it is quite a simple task.
For example, you want to filter the dataset based on Education, a category of the sites.
For doing this, select the Filter option from the Data tab.
Then, check the box before Education and uncheck the rest boxes.
After pressing OK, you’ll get the following filtered rows.
Whatever, let’s come to our topic. We need to copy this type of filtered rows quickly in Excel.
1. Using the Go To Special Option
In the beginning method, I’ll show you a simple tool to copy filtered rows. And that is the application of the Go To Special option actually.
To use the option, you have to select the range of filtered rows (B4:B16).
Next, move the cursor on the Find & Select option in the Editing ribbon of the Home tab.
After clicking on the Find & Select option, you’ll see the Go To Special option and choose the option.
Then, you’ll see a dialog box where you need to check the circle before the Visible cells only option.
After pressing OK, you have to copy the range of filtered rows (e.g. you may copy using Ctrl + C).
Finally, you need to paste the filtered data by pressing Ctrl + V or choosing the Paste Option from the Context Menu (by right-clicking).
In the above picture, you see that the filtered data are copied without any hidden rows.
Note: In the above picture, I copied the filtered rows in the existing working sheet. However, you may accomplish the task in the new working sheet in the same manner.
Read More: How to Copy and Paste Thousands of Rows in Excel (3 Ways)
2. Copy Rows with Excel Filter Using Keyboard Shortcut
If you prefer using the keyboard shortcut, this method will be beneficial for you. Luckily, you don’t need any option or tool to copy filtered rows if you know the shortcuts.
To apply the shortcuts, you have to select the filtered rows then press Alt + ; for selecting the visible cells only.
Later, press Ctrl + C to copy the visible cells.
Ultimately, you need to press Ctrl + V in a new destination.
Thus, you’ll get the same filtered rows as shown in the following screenshot.
Note: In maximum cases, you can copy the filtered rows by pressing simply Ctrl + C to copy and Ctrl + V to paste. In other words, you don’t need to press Alt + ;.
Read More: How to Paste From Clipboard to Excel Using VBA
3. Apply Excel Formula to Copy Rows with Filter
In the first picture, you may find an unexpected issue.
If you look closely at Column F, you’ll get that all filtered rows of Column D are not copied exactly.
Because there exist hidden rows.
So, how can you overcome the dilemma?
Truly, it’s a very easy task. Just insert the following formula in the F5 Cell.
=D5
Here, D5 is the starting cell of the number of visits.
Then, press Ctlr + Enter or use the Fill Handle Tool after pressing the Enter button.
Immediately, you’ll get all filtered rows of Column D.
Read More: How to Copy and Paste in Excel When Filter Is On (5 Methods)
Similar Readings
- How to Copy and Paste Exact Formatting in Excel(Quick 6 Methods)
- Copy a Worksheet in Excel (5 Smart Ways)
- How to Copy and Paste Multiple Cells in Excel (7 Quick Ways)
- Exchange (Copy, Import, Export) Data Between Excel and Access
- How to Use Paste Special Command in Excel (5 Suitable Ways)
4. Copy Filtered Rows Using a Command from the Quick Access Toolbar (QAT)
Probably, you are accustomed to using one of the productive features of Excel i.e. Quick Access Toolbar. You might see the toolbar (yellow-colored in the following picture) in Excel.
Luckily, you can customize the toolbar to copy filtered rows.
For customizing it, you have to click on the toolbar first.
Immediately, you’ll see many options and choose More Commands.
Next, you’ll find a dialog box where you have to select the option sequentially i.e. All Commands > Select Visible Cells > Add > OK.
Eventually, you’ll get the command in the Quick Access Toolbar. Now, click on the command, then press Ctrl + C and use Ctrl + V for pasting the data in a new destination.
Read More: How to Autofilter and Copy Visible Rows with Excel VBA
5. Copy Filtered Rows to New Worksheet Automatically
In the above-discussed 4 methods, you learned how to copy rows in Excel with filters in the existing working sheet. Fortunately, you may accomplish the same task in a new working sheet. But you have to open the new working sheet manually.
How would you feel if the new worksheet opens automatically with the filtered rows?
Obviously, that will be effective and time-saving.
Let’s explore the method.
Advanced Filter tool is really sophisticated to filter the dataset as you can specify any criteria with the filtering.
Whatever, I fixed the criteria range located at the C19:D20 range. That means I want to filter the dataset that covers either the Education category or Web platforms.
While doing this method, you have to be careful at this stage.
When you are going to turn the Advanced filter from the Sort & Filter ribbon in the Home tab, make sure that your active sheet is the new working sheet (e.g. New Sheet1 is the new working sheet in the practice workbook).
Then, you’ll see a dialog box namely Advanced Filter. Now, follow the below things.
Firstly, check the circle before the Copy to another location option.
Later fix the ‘Advanced Filter’!$B$4:$F$16 as the List range (here the name of the existing worksheet is Advanced Filter).
Meanwhile, you have to specify the ‘Advanced Filter’!Criteria as the Criteria range.
Subsequently, you have to appoint the location ‘New Sheet1’!$B$4:$F$4 after the Copy to option.
Finally, press OK and you’ll get the filtered rows in the new working sheet (New Sheet1).
Read More: How to Copy Rows Automatically in Excel to Another Sheet (4 Methods)
6. Using VBA Code to Copy Rows in the New Sheet
Again, if you wish to use the VBA code in Excel to copy rows with filters, you may follow this method.
For example, I want to copy the value of Number of Visits located at the F4:F16 cell range.
To execute the task, follow the below 3 steps.
Step 01: Inserting a Module
Firstly, open a module by clicking Developer > Visual Basic.
Secondly, go to Insert > Module.
Step 02: Copying the VBA Code
Now, copy the following code in the newly created module.
Sub Copy_FilteredRows_NewSheet()
Dim Rng As Range
Dim Selecting_FilteredRows As Range
Dim destination As Range
Dim source As Range
Dim r As Range
Set Rng = Application.Selection
Rng.SpecialCells(xlCellTypeVisible).Select
Set Selecting_FilteredRows = Application.Selection
Set destination = Application.InputBox("Please Select the Output Range:", Type:=8)
For Each source In Selecting_FilteredRows
source.Copy
For Each r In destination
If r.EntireRow.RowHeight <> 0 Then
r.PasteSpecial
Set destination = r.Offset(1).Resize(destination.Rows.Count)
Exit For
End If
Next r
Next source
End Sub
In the above code,
i. At the outset, I declared some variables e.g. Rng, Selecting_FilteredRows, etc.
ii. Later, I used the Selection property to return the objects to the active sheet.
iii. Immediately, I used SpecialCells to specify the cells and fixed xlCellTypeVisible as the cell type (the first argument) of the Special Cells.
iv. Again, I utilized the Selection property to select the Select_FilteredRows.
v. Then I appointed an InputBox to select the output range.
vi. Thereafter, I employed For Loop to copy the selected filtered rows.
vii. In addition, I assigned the If logical statement to get the value for skipping hidden rows.
viii. Lastly, Offset is used to return in a specified input range and Resized property is applied to get the resized output.
Step 03: Running the VBA Code
If you run the code (the keyboard shortcut is F5 or Fn + F5), you’ll get a dialog box first.
So, fix the output range where you need to copy the rows. Here, I appointed ‘New Sheet2’!$B$4:$B$11 as the output range (New Sheet2 is the name of the new working sheet).
When you press OK after selecting the output range, you’ll get the filtered rows.
Read More: Excel VBA to Copy Rows to Another Worksheet Based on Criteria
Conclusion
This is how you can copy rows keeping filters in Excel. I strongly believe this article will be highly beneficial for you. Anyway, if you have any queries or recommendations, please share them in the comments section.
Related Articles
- VBA Code to Compare Two Excel Sheets and Copy Differences
- [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
- How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)
- Difference Between Paste and Paste Special in Excel
- How to Copy Alternate Rows in Excel (4 Ways)
- Copy and Paste in Excel and Keep Cell Size (7 Examples)
- How to Copy and Paste in Excel without Formulas (7 Easy Tricks)
