How to Copy Visible Cells Only without Header Using VBA

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.

Read More: Excel VBA: Select Visible Cells After Autofilter (4 Examples)

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.

Read More: How to Copy and Paste Visible Cells Only in Excel (3 Easy Ways)

Quick Notes

🎯 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.

Conclusion

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!!!

Related Articles

• 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