Change a column's contents to uppercase in all worksheets

Question

I'm trying to loop through each worksheet in my workbook and change the text in column G to upper case, with the header column remaining unchanged.

Sub capitalize_columns()
    
    Dim wb as ThisWorkbook
    Dim ws as Worksheet 
    set wb = ThisWorkbook 
    
    For Each ws in wb.worksheets
         With ws
         Dim last_row as Long 
         last row = ws.Cells(Rows.Count, 1).End(xlUp).Row
         Dim capital_range As Range
         Set capital_range = ws.Range("G2:G" & last_row)    
         capital_range.Value = capital_range.Parent.Evaluate("Index(UPPER(" & name_range.Address & "),)")
         End With
     Next ws
End Sub

The script runs but I it doesn't produce my desired result of capitalizing the everything in column G with the exception of the header.

braX · Accepted Answer · 2021-12-09T04:32:51.203

1

I think you are overcomplicating the upper case part. All you need is UCase() in a loop like shown here:

Sub capitalize_columns()
    Dim ws As Worksheet
    Dim row As Long
    Dim last_row As Long
    
    For Each ws In ThisWorkbook.Worksheets
       With ws
         last_row = .Cells(.Rows.Count, 1).End(xlUp).Row ' use column A to find last row
         For row = 2 To last_row ' start at row 2
           .Range("G" & row) = UCase(.Range("G" & row))
         Next
       End With
     Next ws
End Sub

edited Dec 09 '21 at 04:32

answered Dec 09 '21 at 03:09

braX

11,506
5
20
33

1

Thanks much! This works. Writing long, over-complicated code is part of learning, I suppose. – Possdawgers Dec 09 '21 at 03:49

Siddharth Rout · Answer 2 · 2021-12-09T06:24:34.303

Your code is good! Index(UPPER()) is a faster way as it doesn't loop as mentioned in Convert an entire range to uppercase without looping through all the cells. Your code just needs few fixes.

Fixes:

Declare the objects on the top and not in the loop.
Find the last row of column G and not A. You may not get the true range if the column data is uneven.
Use Option Explicit. It will catch typos like last_row Vs last row and also name_range

Code:

Option Explicit

Sub capitalize_columns()
    Dim wb As ThisWorkbook
    Dim ws As Worksheet
    Dim last_row As Long
    Dim capital_range As Range
    
    Set wb = ThisWorkbook
    
    For Each ws In wb.Worksheets
        With ws
            '~~> Find last row in col G
            last_row = .Cells(.Rows.Count, 7).End(xlUp).Row
            
            Set capital_range = .Range("G2:G" & last_row)
            
            capital_range.Value = .Evaluate("Index(UPPER(" & capital_range.Address & "),)")
        End With
    Next ws
End Sub

Change a column's contents to uppercase in all worksheets

2 Answers2