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.
How to Copy Visible Cells Only Without Header Using VBA: 2 Steps
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 who 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, the 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: How to Autofilter and Copy Visible Rows with Excel VBA
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: Excel VBA: Copy Cell Value and Paste to Another Cell
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.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
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 leave a comment below. Have a great day!!!
Related Articles
- How to Paste From Clipboard to Excel Using VBA
- How to Use VBA PasteSpecial for Formulas and Formats in Excel
- How to Use VBA PasteSpecial to Keep Source Formatting in Excel
- How to Use VBA to Paste Values Only with No Formatting in Excel
- How to Copy Multiple Rows in Excel Using Macro
- Excel VBA: Copy Row If Cell Value Matches
- Excel VBA to Copy Only Values to Destination
- Copy and Paste Values to Next Empty Row with Excel VBA
- VBA Paste Special to Copy Values and Formats in Excel
