VBA & Macro Problems

Copy Visible Excel Cells to a Comma-Separated Clipboard String via VBA

John Wilson Oct 22, 2026 868 views

Question details

The user needs an Excel VBA macro that extracts data from a filtered column, ignoring hidden rows, formats the visible values into a single comma-separated string, and automatically places it on the system clipboard.

How to Copy Visible Excel Cells to a Comma-Separated Clipboard String Using VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Filtering a list of data in a spreadsheet and quickly extracting just the visible results as a single, comma-delimited text string to paste into another application.
Observed behavior: Standard copy operations or basic VBA loops extract all cells in the range, including the hidden or filtered ones, instead of strictly selecting the visible cells.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer tools and macro execution in your spreadsheet settings.

Solution 1Recommended

Use VBA SpecialCells and a Late-Bound DataObject

Implement a VBA script that utilizes the SpecialCells(xlCellTypeVisible) method to skip hidden rows and a late-bound DataObject to copy the formatted string directly to the clipboard.

To accomplish this, the macro must specifically target only the visible cells in the range after filtering. By storing these values into an array, you can quickly format them using the Join function. Pushing the result to the clipboard requires accessing a DataObject, which can be done without adding manual library references by utilizing its specific class ID.

Open the VBA Editor

Press ALT + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor in Excel.

Insert a New Module

In the top menu, click 'Insert' and select 'Module' to create a blank script window.

Write the SpecialCells Loop

Write a loop that iterates through your specific column range using '.SpecialCells(xlCellTypeVisible)'. For example: 'For Each cell In ws.Range("A5:A" & LastRow).SpecialCells(xlCellTypeVisible)'. Store each retrieved value into a predefined array or collection.

Format as a Comma-Separated String

After the loop finishes, use the VBA function 'Join(YourArray, ",")' to collapse the stored array values into a single text string separated by commas.

Send to Clipboard via DataObject

Declare a late-bound DataObject using 'Set objDataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")'. Apply '.SetText' to your joined string, and then execute '.PutInClipboard' to finalize the copy action.

Use VBA SpecialCells and a Late-Bound DataObject

Late-Bound DataObject Advantage: Using the specific class ID '1C3B4210-F441-11CE-B9EA-00AA006B1A69' for the DataObject eliminates the need to manually enable the 'Microsoft Forms 2.0 Object Library' in the VBA Reference settings, preventing common compatibility errors on different machines.

Run Advanced VBA Macros Effortlessly in WPS Spreadsheet

WPS Office provides robust native support for VBA macros, allowing you to run scripts that interact with the clipboard, loop through filtered ranges, and manipulate strings exactly as you would in Microsoft Excel.

1. Open your Macro Workbook in WPS Office: Download and install WPS Office, then open your .xlsm file containing your filtered data in WPS Spreadsheet.
2. Access the Developer Tools: Navigate to the 'Tools' tab on the ribbon and click on 'Developer' to access the VBA options.
3. Execute the Macro: Open the VBA Editor to paste your clipboard macro, or simply run it from the Macros list to instantly copy your visible cells as a comma-separated string.

Fully compatible with Microsoft Excel VBA syntax, objects, and properties.Seamlessly executes complex range selections like SpecialCells(xlCellTypeVisible).Lightweight and highly responsive alternative with a dedicated Developer tab.Supports saving and executing .xlsm macro-enabled files seamlessly.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my standard VBA loop copy hidden rows when a filter is applied?

A standard 'For Each cell in Range' loop processes every single cell within the defined coordinates regardless of its current visibility state. To instruct Excel to skip hidden rows, you must explicitly append '.SpecialCells(xlCellTypeVisible)' to the range object.

Do I need to enable specific references for the clipboard DataObject to work?

If you are declaring the standard 'MSForms.DataObject' in early binding, you must manually enable the 'Microsoft Forms 2.0 Object Library' under Tools > References. However, utilizing the CreateObject method with the late-bound class ID ('new:{1C3B...}') bypasses this requirement completely.

Can I change the delimiter from a comma to a line break or semicolon?

Yes. The delimiter is determined by the string inside the Join function. To use semicolons, change 'Join(values, ",")' to 'Join(values, ";")'. For line breaks, replace the comma string with the built-in VBA constant 'vbCrLf'.

How can I trigger this macro without opening the VBA editor every time?

You can assign the macro to a button directly on your worksheet. Go to the Developer tab, select Insert > Button (Form Control), draw it on the sheet, and assign your newly created clipboard macro to it. Clicking the button will then run the script instantly.

Excel & Data Problems