Uppercase and Size

Posted by Anmol on January 29, 2002 12:30 PM

I have two columns with data in a Excel file:

first column has lowercase data which need to be changed to uppercase

second column has some security no which need to be only 9 characters long if any empty spaces then remove it

please help

Posted by gregc on January 29, 2002 1:17 PM

For the CAPS problem paste this code in a macro and highlight the column you need switched to caps and run the macro.

Application.ScreenUpdating = False

While Len(ActiveCell) &LT;&GT; 0
ActiveCell = UCase(ActiveCell)
ActiveCell.Offset(1, 0).Select
Wend

Posted by JohnG on January 29, 2002 1:41 PM

Try this

Sub Macro1()
Dim FinalRow As Integer
Dim Cloop As Integer
Dim Xloop As Integer
Dim TString As String
'
' Convert column A to uppercase
'
FinalRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
For Cloop = 1 To FinalRow
Worksheets("Sheet1").Range("A" & Cloop).Value = UCase(Worksheets("Sheet1").Range("A" & Cloop).Value)
Next
'
'Convert column B without spaces
'
FinalRow = Worksheets("Sheet1").Range("B65536").End(xlUp).Row
For Cloop = 1 To FinalRow
TString = ""
For Xloop = 1 To Len(Worksheets("Sheet1").Range("B" & Cloop).Value)
If Mid(Worksheets("Sheet1").Range("B" & Cloop).Value, Xloop, 1) &LT;&GT; Chr$(32) Then
TString = TString & Mid(Worksheets("Sheet1").Range("B" & Cloop).Value, Xloop, 1)
End If
Next Xloop
Worksheets("Sheet1").Range("B" & Cloop).Value = TString
Next Cloop
End Sub