From column to single cell

[22strider]

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

 

[Tom Urtis]

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:

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

 

[22strider]

Thanks Tom;

Your code worked very well.

Regards,
Rajesh

 

[dfsobral]

hi tom

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

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

 

[Tom Urtis]

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.

 

[dfsobral]

ok
thanks for the explanation