1

I am trying to select all the data in a column and make it proper case.

This is what I'm starting with:

Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select

I think I want to use Application.WorksheetFunction.Proper.

Community
  • 1
  • 1
TastyPlacebo
  • 13
  • 3
  • See [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last used cell. – BigBen Apr 22 '21 at 17:11

2 Answers2

1

Here is my approach to this, adjust the column index (rw.Row, 1) to suit your project

Sub ConvertValuesInColumnOneToProperCase()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = ActiveSheet

    Dim rw As Range

    For Each rw In ws.UsedRange.Rows
        ws.Cells(rw.Row, 1) = StrConv(ws.Cells(rw.Row, 1), vbProperCase)
    Next rw

End Sub
ShadowDragon
  • 61
  • 4
0

Please, try this way. The next code processes the values in column E:E. You can easily adapt it for another column. No need to select anything. Selecting, activating only consumes Excel resources without bringing any benefit:

Sub UcaseRange()
 Dim sh As Worksheet, rng As Range
 
  Set sh = ActiveSheet
  Set rng = sh.Range("E2:E" & sh.Range("E" & sh.rows.count).End(xlUp).row)

  rng = Evaluate("INDEX(UPPER(" & rng.Address(0, 0) & "),)")
End Sub
FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • Can you point me to a general resource that explains what to use instead of selecting? As I said most of my macros are built from the record function and I don't know how to write code on its own. – TastyPlacebo Apr 22 '21 at 17:39
  • @TastyPlacebo: I thought it should be obvious looking to the above code... The range is built by concatenation of the "E2:E" string with the **last row number**. Obtained using `sh.Range("E" & sh.rows.count).End(xlUp).row`. Is it now clearer? – FaneDuru Apr 22 '21 at 17:42
  • Nothing is obvious for me as I am completely new to writing code of any kind. That is more clear thank you. I changed the code you shared to the column I need, and made the change from UPPER to PROPER and it works perfectly, thank you. – TastyPlacebo Apr 22 '21 at 17:43