How to Copy Visible Cells Only without Header Using VBA

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
ASM Arman
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo