After filtering data using the auto filter in Excel, we need to copy visible cells only without a header. We can do this very easily using the VBA macro in Excel. In this tutorial, I will show you how to copy visible cells only without a header using VBA.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
2 Steps to Copy Visible Cells Only Without Header Using VBA
Let’s assume a scenario where we have an Excel worksheet that has information about the products a company sold to the customers. The Excel worksheet has the Product Name, Product Category, Sales Person that sold the product, and the Shipping address to deliver the product. Now we will use the text filter in this Excel worksheet to filter the text values and then use the VBA to copy the visible cells only without the column header into another worksheet. The image below shows the worksheet that we are going to work with.
Step 1: Filter the Data Using the Auto Filter
- First, we will select a cell in our data range, and then we will go to the Data. Next, we will select the Filter option from the Sort & Filter.
- After clicking the Filter option, we will see a small downward arrow on the down-right corner of each column header.
- We will click a downward arrow on the Product A window will appear with options that you can use to filter the information in the Product column.
- You will see an option named Text Filters. Under the Text Filters option, names of all the unique products in the Product column are listed and each has a select box beside them. We will uncheck the Select All. We will then select the LED Monitor and USB Keyboard.
- Finally, we will click Ok.
- Now, we will see the worksheet has been filtered and it has only those rows that have LED Monitor or USB Keyboard in the Product
- The small arrow on the down-right corner of the product column has been changed to the Filter The Filter icon on the Product column indicates that the data has been filtered based on this column.
Step 2: Insert the VBA Code to Copy Visible Cells Only Without Header
- First, we will select Visual Basic from the Developer. We can also press ALT+F11 to open it.
- Now, click on the Insert button and select Module.
- Then, write down the following code in the window that appears. This code will copy the visible filtered cells into another worksheet named Copy.
Option Explicit Sub CopyVisibleData() Dim DestSheet As Worksheet Dim FiltRng As Range Dim CellNum As Long Dim Msg As String With ActiveSheet If .AutoFilterMode = False Or .FilterMode = False Then MsgBox "Filter the data with the AutoFilter First!" Exit Sub End If End With Set DestSheet = Worksheets("Copy") DestSheet.Cells.Clear With ActiveSheet.AutoFilter.Range If Val(Application.Version) < 14 Then On Error Resume Next CellNum = .Columns(1).SpecialCells(xlCellTypeVisible) _ .Areas(1).Cells.Count On Error GoTo 0 If CellNum = 0 Then Msg = "The SpecialCells limit has been " & vbNewLine & "exceeded for the filtered value." & vbNewLine & vbNewLine & "Tip: Sort the data!" MsgBox Msg, vbExclamation, "SpecialCells Limitation" GoTo ExitTheSub End If End If On Error Resume Next Set FiltRng = .Resize(.Rows.Count - 1).Offset(1, 0) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not FiltRng Is Nothing Then FiltRng.Copy Destination:=DestSheet.Range("B2") Else MsgBox "No record to copy...", vbExclamation End If End With ExitTheSub: ActiveSheet.ShowAllData End Sub
- Now, click on the Run (▶).
- Finally, if we go back to the new worksheet named Copy, we will see that we have been able to copy visible cells only without a column header using the VBA.
🎯 If you do not have a Developer tab, you can make it visible in File > Option > Customize Ribbon.
🎯 To open the VBA editor Press ALT + F11. You can press ALT + F8 to bring up the Macro window.
In this article, we have learned how to copy visible cells only without a header using VBA in Excel. I hope from now on you can copy visible cells only without a header using VBA in Excel very easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!
- How to Select Visible Cells in Excel with VBA (5 Easy Methods)
- Copy Rows in Excel with Filter (6 Fast Methods)
- Sum Visible Cells with Criteria in Excel (5 Easy Methods)
- Copy and Paste in Excel When Filter Is On (5 Methods)
- How to Average Only Visible Cells in Excel (3 Quick Methods)
- Copy Merged and Filtered Cells in Excel (4 Methods)
- Excel VBA to Select First Visible Cell in Filtered Range