Excel macro to convert data in a column

Question

I was generously given this macro to run in excel to convert columns with multiple data types to alphanumeric. It works great except that it keeps going indefinitely, and does not stop when the last row is completed. Since I am weak in this area, can someone tell me how to make the macro stop at the end of the worksheet?  Some rows MAY not have a value in that cell, so I can't simply tell it to quit if a cell's contents are blank. Thank you so much for any help.

Sub ConvertToAlphanumeric()

'highlight the desired column before running the macro

Dim cell As Object

On Error GoTo out

For Each cell In Selection

cell.Value = "'" & cell.Value

ActiveCell.Offset(1, 0).Activate

Next

out:

MsgBox "This column has been converted to proper text format."

End Sub

Answer 1

Thank you Gord...Almost... It is converting every column in my worksheet... not just the columns I highlighted. Can you show me how to limit it to the columns I highlight, even if I highlight 3 that are right next to each other?  

Hi Karen Anne,

To restrict the action of Gord's suggested code to the selected columns, 

try the following minor amendment:

'==========>>

Public Sub Add_Apostrophe()

Dim myCel As Range

Dim myRng As Range

Set myRng = Intersect(Selection.EntireColumn, ActiveSheet.UsedRange)

For Each myCel In myRng.Cells

If myCel.Value <> "" Then

With myCel

.Value = "'" & .Value

End With

End If

Next

End Sub

'<<==========

===

Regards

Norman

Answer 2

Hi,

Try this instead:

Sub ConvertToAlphanumeric()

' Converts the column of the active cell to text.'    Dim rng As Range, c As Range

    Dim lnLastRow As Long, lnColNum As Long

    Dim strColLetter As String

    lnColNum = ActiveCell.Column

    lnLastRow = Cells(Rows.Count, lnColNum).End(xlUp).Row

    Set rng = Range(Cells(1, lnColNum), Cells(lnLastRow, lnColNum))

    For Each c In rng

        c.Value = "'" & c.Value

    Next c

    strColLetter = Mid(Columns(lnColNum).Address, InStrRev(Columns(lnColNum).Address, "$") + 1)

    MsgBox "Column '" & strColLetter & "' has been converted to proper text format."

End Sub

Cheers

Rich

Answer 3

Beautiful, thank you. I can only do one column at a time with this, but its better than what I had before. Thanks!

Answer 4

Thank you Gord...Almost... It is converting every column in my worksheet... not just the columns I highlighted. Can you show me how to limit it to the columns I highlight, even if I highlight 3 that are right next to each other?

Answer 5

For usedrange, not just one column. . . . . .may come in handy.

Sub Add_Apostrophe()

Dim myCel As Range

Dim myRng As Range

    Set myRng = ActiveSheet.UsedRange

    For Each myCel In myRng

         If myCel.Value <> "" Then

    myCel.Value = "'" & myCel.Value

    End If

    Next

    Range("A1").Select

End Sub

Gord