Excel VBA: upper case space replacement for selected row

Luis Diaz
Luis Diaz used Ask the Experts™
on
Hello experts,

Excel VBA: upper case space replacement
I would like to add to my personal.xlsb the following procedure.
1.      Display an input box with the following message: “Please select the row in which you want to upper case and replace spaces”.
2.      Upper case the various values and replace space by “_”.
3.      Log error handling: if user click on cancel button related to input box.
If you have questions, please contact me.
Thank  you very much for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please give this a try...
Sub UpperCaseAndReplaceSpaceWithHyphen()
Dim ws As Worksheet
Dim RowRange As Range, aCell As Range
Dim lr As Long, lc As Long, Col As Long
On Error Resume Next
Set RowRange = Application.InputBox("Please select the row in which you want to upper case and replace spaces.", "Select Row!", Type:=8)
On Error GoTo 0

If RowRange Is Nothing Then
    MsgBox "You didn't select any row.", vbExclamation, "Action Cancelled!"
    Exit Sub
End If
Application.ScreenUpdating = False
lr = RowRange.Row
Set ws = RowRange.Parent
lc = ws.Cells(lr, Columns.Count).End(xlToLeft).Column
For Col = 1 To lc
    ws.Cells(lr, Col) = Replace(UCase(ws.Cells(lr, Col)), " ", "-")
Next Col
Application.ScreenUpdating = True
End Sub

Open in new window

Luis DiazIT consultant

Author

Commented:
Thank you very much. I will test it today.
Luis DiazIT consultant

Author

Commented:
Tested and it works! Thank you very much for your help!
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome! ☺️

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial