Excel VBA: Add specific value on multiple columns v2

Sub Add_Specific_String_Multiple_Columns()

    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long, lngRow As Long
    Dim strToReplaceWith As String
    
    
    strToReplaceWith = InputBox("Please input the Value which should replace the values of the Reported Columns.")
    
    If strToReplaceWith = "" Then
        MsgBox "You didn't input any value.", vbExclamation
        Exit Sub
    End If
    

    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
                          & ": A for single column A;C;D for multiple columns", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If
    
    For Each strCol In Split(strColList, ";")
        If Not ValidCellReference(strCol) Then
            MsgBox strCol & " is not a valid column letter.", vbExclamation
            Exit Sub
        End If
    Next strCol
    
    For Each strCol In Split(strColList, ";")
        lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row
        Range(strCol & "2:" & strCol & lngLastRow).Value = strToReplaceWith
    Next
End Sub

Function ValidCellReference(ByVal str As String) As Boolean
Dim rng As Range
On Error Resume Next
Set rng = Cells(1, str)
On Error GoTo 0
If Not rng Is Nothing Then ValidCellReference = True
End Function

Select allOpen in new window

       Range(strCol & "2:" & strCol & lngLastRow).Value = strToReplaceWith

Select allOpen in new window