Setting proper case to a range using xlUp

Question

I have the code below that I would like to use to set the proper casing for a range that can be 20 records or can be 500 records as the data changes daily. The problem is that takes long because it is evaluating blank records. I want it to just set the proper casing to the records and stop at the blank rows. I know I have to use either XlDown or XLup but cannot seem to get the syntax right. The range starts at V2 and ends at the first blank row.

Sub propercase_test()
Dim LCRange As Long
   LCRange = 
   Sheets("Profitability").Cells(Sheets("Profitability").Rows.Count, 
   "V").End(xlUp).Row
For Each Rng In Range(LCRange)
    Rng.Value = Application.WorksheetFunction.Proper(Rng.Value)
Next Rng
End Sub

Edited code:

Sub propercase_test()
Dim LCRange As Range

With Sheets("Profitability")
  LCLastRow = .Range("V" & .Rows.Count).End(xlUp).Row
  Set LCRange = Range("V" & LCLastRow)
End With

For Each Rng In Range(LCRange)
  Rng.Value = Application.WorksheetFunction.Proper(Rng.Value)
Next Rng
End Sub

Possible duplicate of [Error in finding last used cell in Excel with VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) — OwlsSleeping, Aug 17 '19 at 13:51
Thanks owlssleeping, that helped me find the last row but I can't figure out how to incorporate into my code. — MEC, Aug 17 '19 at 14:19
If you post your updated code I'll take a look at the loop. Biggest issue at first glance is that LCRange is Long rather than Range. — OwlsSleeping, Aug 17 '19 at 14:22

OwlsSleeping · Accepted Answer · 2019-08-17T16:13:19.560

0

Looping through range

Sub propercase_test()

Dim LCLastRow As Long
Dim LCRange As Range

With Sheets("Profitability")
  LCLastRow = .Range("V" & .Rows.Count).End(xlUp).Row
  Set LCRange = .Range("V2:V" & LCLastRow)
End With

Dim rng As Range    
For Each rng In LCRange
  rng.Value = Application.WorksheetFunction.Proper(rng.Value)
Next rng
End Sub

Looping through int

Sub propercase_with_int()

Dim LCLastRow As Long

With Sheets("Profitability")
  LCLastRow = .Range("V" & .Rows.Count).End(xlUp).Row
End With

Dim RowNum As Long
For RowNum = 2 To LCLastRow
  Cells(RowNum, 22).Value = Application.WorksheetFunction.Proper(Cells(RowNum, 22).Value)
Next RowNum

End Sub

edited Aug 17 '19 at 16:13

answered Aug 17 '19 at 14:45

OwlsSleeping

1,487
2
11
19

Thank you for your help, that worked perfectly. Have to understand the long and the range :-) – MEC Aug 17 '19 at 15:41
It's possible to loop through both integers and ranges. I'll add the int example for contrast – OwlsSleeping Aug 17 '19 at 16:10

Setting proper case to a range using xlUp

1 Answers1