Capitalize all Letters in Column B..rows will Vary

Hello everybody,

Does anybody know an easy macro that will make every letter in column B Upper case. But the only thing is the rows will vary each time i run the macro so it will need to run until the last row in the column is UpperCase. Thanks for your help.

This works martindwilson but is making Columns C and D upper case as well. Is there a way to make it just for column B. And Mumps1 this coding is part of a bigger macro. I import all the data so thats why I'm trying to find a way to have the macro convert Column B only to uppercase. I appreciate the help.

Hi Trevor. Place this macro in the worksheet code module (not a regular module). The text in column B will automatically be converted to uppercase as you enter the data in each cell in column b and then exit the cell. You don't have to run any macro. This will be done automatically. Does this work for you?

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim bottomB As Long
    bottomB = Range("B" & Rows.count).End(xlUp).Row
    If Not Application.Intersect(Target, Range("B1:B" & bottomB)) Is Nothing Then
        Target(1).value = UCase(Target(1).value)
    End If
    Application.EnableEvents = True
End Sub

Hi does anybody know how to fix that one problem regarding it upper-casing Columns C and D and not just Column B. Sorry for the continuous posts I'm in kind-of a time crunch.

Sub UpperCase()
    
    Application.ScreenUpdating = False

    Dim cell As Range
    For Each cell In Range("$B$1:B" & Range("$B$1").SpecialCells(xlLastCell).row)
        If Len(cell) > 0 Then cell = UCase(cell)
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

or using mumps version

Sub UpperCase()
    
    Application.ScreenUpdating = False

    Dim cell As Range
    For Each cell In Range("$B$1:B" & Range("B" & rows.count).end(xlup).row)
        If Len(cell) > 0 Then cell = UCase(cell)
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

Thank You!

Hi Trevor. Have you tried my suggestion? I tried it on a dummy sheet and it worked for me. The advantage to using a Worksheet_Change event is that you don't have to keep running the macro each time. The data is converted automatically after you enter it.