From column to single cell

22strider

Active Member
Joined
Jun 11, 2007
Messages
311
Hello Friends,

Could you please help with VBA code for doing following:

1) Read list of values in a single column
2) Enter all those values delimited by semi colon in to another single cell.

For example following Input values in Column A
Input Values (Heading)
100
200
300
400

Will appear in a single cell as 100;200;300;400

Regards,
Rajesh
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Assuming...
• Your list in column A has its header cell in A1
• Your destination cell is B1
• You do not want a semicolon at the very end of the entire string as you presented your expected result
• You do not want a space and a semicolon, only a semicolon, as the delimiter as you presented your expected result
• You do not want a null string to represent empty or blank (there's a difference) cells
...then this would do what you want:

Code:
Sub Test1()
Dim strValue$, cell As Range, LastRow&
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each cell In Range("A2:A" & LastRow).SpecialCells(2)
strValue = strValue & cell.Value & ";"
Next cell
strValue = Left(strValue, Len(strValue) - 1)
Range("B1").Value = strValue
End Sub
 
Upvote 0
hi tom

i've notice you place some special characters when declaring variables at the end of the name like $ or &

Code:
Dim strValue$, cell As Range, LastRow&

i pretty much learn about code by myself, have seen that sometimes and have no idea why neither found any tutorial that mention it
don't know if doing that allows a better code or if it's a personal way of identifying the variables

if it's something i should learn can you advise me a tutorial

thanks
 
Upvote 0
It's certainly not necessary to learn the VBA variable type abbreviations but if you do learn them, or at least you become aware of their existence as you are doing now, you can hold your own if the topic should come up at the water cooler. These abbreviations are not common and they do not add efficiency value to the code from an execution standpoint, but for me they are easier and faster to type once you learn them. I post them here because it's my way of showing a different way of expressing variable types as numeric constants for people who might appreciate that alternative if they were not aware of it.

You might have also noticed in my code .SpecialCells(2) which is the same as .SpecialCells(xlCellTypeConstants); same idea for abbreviations.

Other variable type abbreviations are:
String $
Integer %
Long &
Single !
Double #
Currency @

I don't advocate these if you write code that will be managed or modified later by others who may not be familiar with constants, hence to them see your code as being cryptic, and for that reason there are naysayers who advise against the practice. But in my programs for my clients, I modify everything that needs modifying, so the abbreviations have worked well for me.

Thanks for your question.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top