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.

How to Copy Visible Cells Only VBA Without Header


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.

Filter the Data Using the Auto Filter

  • After clicking the Filter option, we will see a small downward arrow on the down-right corner of each column header.

Insert the VBA Code to Copy Visible Cells Only Without 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.

Filter the Data Using the Auto Filter

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

Filter the Data Using the Auto Filter

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.

Insert the VBA Code to Copy Visible Cells Only Without HeaderInsert the VBA Code to Copy Visible Cells Only Without Header

  • Now, click on the Insert button and select Module.

Insert the VBA Code to Copy Visible Cells Only Without Header

  • 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 ().

Insert the VBA Code to Copy Visible Cells Only Without Header

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

Insert the VBA Code to Copy Visible Cells Only Without Header

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

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo