Merging Multiple Cells into Single One in Excel

These are very simple and useful macros to concatenate the content of multiple cells, merging it into one cell.

'Macros for Horizontal and Vertical concatenation

'Copyright, Andrés Gonzalez, 2008

Sub mezclar() 'horizontal concatenation

For Each fila In Selection.Rows

mensaje = ""

For Each celda In fila.Cells

mensaje = mensaje & celda.Value & " "

Next

fila.Clear

fila.Cells(1, 1).Value = mensaje

Next

End Sub

Sub mezclarV() 'vertical concatenation

For Each columna In Selection.Columns

mensaje = ""

For Each celda In columna.Cells

mensaje = mensaje & "- " & celda.Value & Chr(10)

Next

columna.Clear

columna.Cells(1, 1).Value = mensaje

Next

End Sub

Let's say you have the following table:

Albert	Einstein
Nikola	Tesla

With the first macro the result will be:

albert einstein

nikola tesla

And with the second, it will be:

- albert - nikola

- einstein - tesla

I think this one is cool, isn't it?

Lower-case Upper-case Macros on Excel

These are to simple macros to convert cells between uppercase and lowercase. I know it's very easy in Word... but I haven't found that damn button on Excel...

'This two macros replace the strings in a range to their

'equivalent in upper-case or lower-case

'Copyright, Andrés Gonzalez, 2008

Sub MINUSMAYUS()

For Each celda In Selection

celda.Value = StrConv(celda.Value, vbUpperCase)

Next

End Sub

Sub MAYUSMINUS()

For Each celda In Selection

celda.Value = StrConv(celda.Value, vbLowerCase)

Next

End Sub

Find a File's Modification Date in Excel

'This function gives the date of last modification of a given file

'example: fecharchivo("c:\testfile.txt")

' result: "15/12/2011 07:35:29 p.m."

'Copyright, Andrés González, 2008

Function fecharchivo(abrir As String) As Variant

Dim fs, f, s

Set fs = CreateObject("Scripting.FileSystemObject")

Set f = fs.GetFile(abrir)

fecharchivo = f.DateLastModified

fecharchivo = Format(fecharchivo, "General Date")

End Function