More efficient way of capitalizing all cells in a range

naahS

New Member
Joined
Jan 19, 2023
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi, I'm very new to VBA so apologies if this is a simple question. I was using a macro to capitalize all strings in two ranges. The current code I'm using looks like this:

VBA Code:
Dim x As Range

For Each x In SuppliesList.Range("B" & FilledRows + 1, "C" & SourceRows + FilledRows)
 x.Value = UCase(x.Value)
Next

For Each x In SuppliesList.Range("J" & FilledRows + 1, "O" & SourceRows + FilledRows)
 x.Value = UCase(x.Value)
Next

This loops through every cell and capitalizes them individually, which doesn't seem very efficient. I am concerned about speed as I wanted to run this on many workbooks within a folder, so any gains in processing time are multiplied. Is there a way to write this macro so that it instead looks at the entire range at once and capitalizes everything in a single action? Would this actually make the macro run meaningfully faster?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Untested so test on a copy of your data

VBA Code:
Sub capitals1()
Dim MyRng1 As Range, MyRng2 As Range

Set MyRng1 = SuppliesList.Range("B" & FilledRows + 1, "C" & SourceRows + FilledRows)
Set MyRng2 = SuppliesList.Range("J" & FilledRows + 1, "O" & SourceRows + FilledRows)

MyRng1 = Evaluate("INDEX(UPPER(" & MyRng1.Address(External:=True) & "),)")
MyRng2 = Evaluate("INDEX(UPPER(" & MyRng2.Address(External:=True) & "),)")

End Sub
 
Upvote 0
Solution
Hi and welcome to MrExcel!

SuppliesList.Range("B" & FilledRows + 1, "C" & SourceRows + FilledRows)
SuppliesList.Range("J" & FilledRows + 1, "O" & SourceRows + FilledRows)

I don't understand the logic of how you structure the range of cells, but if it works for you with that data, try the following macro, it processes the information in memory and then puts the results in a single output.

Try this:

VBA Code:
  Dim rng1 As Range, rng2 As Range

  Set rng1 = SuppliesList.Range("B" & FilledRows + 1, "C" & FilledRows + FilledRows)
  Set rng2 = SuppliesList.Range("J" & FilledRows + 1, "O" & SourceRows + FilledRows)

  a = rng1.Value
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(a, 2)
      a(i, j) = UCase(a(i, j))
    Next
  Next
 
  b = rng2.Value
  For i = 1 To UBound(b, 1)
    For j = 1 To UBound(b, 2)
      b(i, j) = UCase(b(i, j))
    Next
  Next
 
'Output
  rng1.Value = a
  rng2.Value = b
 
Upvote 0
Untested so test on a copy of your data

VBA Code:
Sub capitals1()
Dim MyRng1 As Range, MyRng2 As Range

Set MyRng1 = SuppliesList.Range("B" & FilledRows + 1, "C" & SourceRows + FilledRows)
Set MyRng2 = SuppliesList.Range("J" & FilledRows + 1, "O" & SourceRows + FilledRows)

MyRng1 = Evaluate("INDEX(UPPER(" & MyRng1.Address(External:=True) & "),)")
MyRng2 = Evaluate("INDEX(UPPER(" & MyRng2.Address(External:=True) & "),)")

End Sub

This works perfectly, thanks.
 
Upvote 0
I don't understand the logic of how you structure the range of cells

This is part of a larger macro I was putting together for the purpose of extracting data from purchase orders and reformatting and pasting it into a master list of supplies purchased for the year. The FilledRows variable counts the number of rows that are already filled in the supplies list workbook and the SourceRows variable counts the number of items to be copied over from the PO. There's two sets of columns in the supplies list that need to be capitalized, so those ranges were the best way I could come up with to define the areas that need to be capitalized.
 
Upvote 0
This is part of a larger macro I was putting together for the purpose of extracting data from purchase orders and reformatting and pasting it into a master list of supplies purchased for the year. The FilledRows variable counts the number of rows that are already filled in the supplies list workbook and the SourceRows variable counts the number of items to be copied over from the PO. There's two sets of columns in the supplies list that need to be capitalized, so those ranges were the best way I could come up with to define the areas that need to be capitalized.


Thanks for the explanation.
I did a small adjustment to the range, try the following macro and review the times with a large range of data to compare which is more efficient.

VBA Code:
  Dim rng1 As Range, rng2 As Range
  Set rng1 = SuppliesList.Range("B" & FilledRows + 1, "C" & SourceRows + FilledRows)
  Set rng2 = SuppliesList.Range("J" & FilledRows + 1, "O" & SourceRows + FilledRows)

  a = rng1.Value
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(a, 2)
      a(i, j) = UCase(a(i, j))
    Next
  Next
  
  b = rng2.Value
  For i = 1 To UBound(b, 1)
    For j = 1 To UBound(b, 2)
      b(i, j) = UCase(b(i, j))
    Next
  Next
  
  rng1.Value = a
  rng2.Value = b
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top