More efficient way of capitalizing all cells in a range

[naahS]

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:

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?

 

[MARK858]

Untested so test on a copy of your data

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

 

[DanteAmor]

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:

  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

 

[naahS]

Untested so test on a copy of your data

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.

 

[MARK858]

This works perfectly, thanks.

You're welcome

 

[naahS]

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.

 

[DanteAmor]

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.

  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