«

»

Jul
26
2013

How to Convert Select Excel Cells into Comma Separated Values (CSV) with VBA

Here is a simple yet powerful technique to convert selected cell values (rows or columns or mix of cells) into comma separated values (CSV) or delimited text format with the help of VBA. This will be useful when you want to merge cell values together quickly (from a key board shortcut for example) and use those values into search fields in web pages/web applications.

 

 

Sub SelectCellsToCSV()

Dim buildStr As String

Dim oCell As Range

Dim tempStr As String

Dim iDed As Integer

Dim clipboard As MSForms.DataObject

Set clipboard = New MSForms.DataObject

buildStr = ""

iDed = 0

For Each oCell In Selection.SpecialCells(xlCellTypeVisible)

tempStr = oCell.Value

If iDed = 0 Then

buildStr = tempStr

Else

'Ignore the blank cells on the selection'

If (Len(tempStr)>0) Then

buildStr = buildStr + "," + tempStr

End If

End If

'use this variable if you need the count'

iDed = iDed + 1

Next

clipboard.SetText buildStr

clipboard.PutInClipboard

End Sub

This VBA procedure navigates through the selected cells and merges their values together with comma (,) symbol. The code also ignores the blank cells.

The merged cell values as string will be copied into clip board.

Note: If the code does not work then make sure the reference – Microsoft Form 2.0 Object Library is added into your library (From macros window navigate to Tools > References and add this reference). This library is required for accessing clipboard.

 

Converting Excel Cell values into CSV

 

You can assign this code into a shortcut key or to a custom button for quick access. If you are unsure about how then refer these posts –

How to assign a keyboard shortcut for an Excel VBA Macro

Assigning Excel Macros to Custom Buttons

 

Did you find this post useful? Feel free to share your feedback in the comments area.

You can find more interesting posts from us on the post index section

If you are interested to be notified for the new posts from us, then you can make use of the subscribe option available on the right sidebar.

Help us to improve our search rating by recommending this post to Google using the button below

 

Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>