Change a column's contents to uppercase in all worksheets-CodePudding

I'm hoping someone can help me out here. 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. I feel like I'm part of the way there, but I must be overlooking something.

Here's the approach I have tried:

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 in excel, but I it doesn't produce my desired result of capitalizing the everything in column G with the exception of the header.

Can someone please tell me what I'm missing here? I'm at a loss.

Thanks so much!

CodePudding user response：

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

CodePudding user response：

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