Proper case -- speed issue

[wmtsub]

I use this on many work sheets and it works well. But i am working on a table and I think it is doing the entire possible range rather than the populated range. How can I modify this so it only works on populated cells?



Sub ProperCase()
awsn = ActiveSheet.Name
ib = InputBox("Count What Row")
Dim LastRow As Integer
With Worksheets(awsn)
LastRow = .Cells(Rows.Count, ib).End(xlUp).Row
.Range("A2:xfd" & LastRow).Value = .Evaluate("INDEX(PROPER(A2:xfd" & LastRow & "),)")
End With
End Sub

 

[Rick Rothstein]

I use this on many work sheets and it works well. But i am working on a table and I think it is doing the entire possible range rather than the populated range. How can I modify this so it only works on populated cells?

Sub ProperCase()
awsn = ActiveSheet.Name
ib = InputBox("Count What Row")
Dim LastRow As Integer
With Worksheets(awsn)
LastRow = .Cells(Rows.Count, ib).End(xlUp).Row
.Range("A2:xfd" & LastRow).Value = .Evaluate("INDEX(PROPER(A2:xfd" & LastRow & "),)")
End With
End Sub

I presume what I highlighted in red should read "Column", not "Row", correct?

Question: Are you trying to do this for all of the filled cells on the active sheet or are you asking the above question so that some rows below the calculated last row will be left alone?

 

[mole999]

how many columns ?

 

[ParamRay]

Going on the assumption that you're trying to convert all cells in the active sheet to proper case.

Public Sub ConvertActiveSheet()
  If TypeOf ActiveSheet Is Worksheet Then
    ConvertTextToProperCase ActiveSheet
  End If
End Sub

'-----------------------------------------------------------------------------

Private Sub ConvertTextToProperCase(ByVal Sh As Worksheet)

' Method to convert all text constants on
' the worksheet argument to proper case.

  Dim TextConstants As Range
  Dim Cell As Range
  
  On Error GoTo ErrorHandler
  Set TextConstants = Sh.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
    
  For Each Cell In TextConstants.Cells
    Cell.Value = StrConv(Cell.Text, vbProperCase)
  Next Cell
  
ExitHandler:
  Set TextConstants = Nothing
  Set Cell = Nothing
  Exit Sub
  
ErrorHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

 

[SpillerBD]

Nice. I hardly use the Goto (F5) when working in Excel. Definitely helps reduce the cells to be changed.

 

[wmtsub]

tks

 

[Rick Rothstein]

Going on the assumption that you're trying to convert all cells in the active sheet to proper case.

Public Sub ConvertActiveSheet()
  If TypeOf ActiveSheet Is Worksheet Then
    ConvertTextToProperCase ActiveSheet
  End If
End Sub

'-----------------------------------------------------------------------------

Private Sub ConvertTextToProperCase(ByVal Sh As Worksheet)

' Method to convert all text constants on
' the worksheet argument to proper case.

  Dim TextConstants As Range
  Dim Cell As Range
  
  On Error GoTo ErrorHandler
  Set TextConstants = Sh.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
    
  For Each Cell In TextConstants.Cells
    Cell.Value = StrConv(Cell.Text, vbProperCase)
  Next Cell
  
ExitHandler:
  Set TextConstants = Nothing
  Set Cell = Nothing
  Exit Sub
  
ErrorHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

I would suggest changing the highlighted line of code to this...

Cell.Value = Application.Proper(Cell.Text)

as Excel's Proper function is much better than VB's StrConv/vbProperCase function (VB's function mishandles text after dashes, parentheses, and such).

 

[ParamRay]

I would suggest changing the highlighted line of code to this...

Cell.Value = Application.Proper(Cell.Text)

as Excel's Proper function is much better than VB's StrConv/vbProperCase function (VB's function mishandles text after dashes, parentheses, and such).

Thanks for the tip. Didn't know that.

 

[wmtsub]

thanks guys