vba - 按行连接所有数据并删除数字

Question

我需要帮助为 excel 编写代码，该代码将所有活动数据放在一行中并将其连接到一个单元格中，然后让这个循环遍历所有活动行。

我遇到的问题是列和行的数量是完全随机的，所以我只需要它来考虑所有现有数据。

最后，我希望串联只包含字母，所以没有数字或“，”或其他任何东西。

如果你有能力请帮忙。

这是之前和之后的图像，如果有帮助的话。

前

后

标签： vbaexcelexcel-formula

Answer 1

这可能有效 - 宏避免了每个特殊字符和数字。将该代码复制并粘贴到 VBA 编辑器中的新模块中，然后运行子“CreateString”。

Sub CreateString()
    Dim LastRow%: LastRow = ActiveSheet.UsedRange.Rows.Count
    Dim strConcatenate$, i%, j%
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.ActiveSheet

    ws.Range("A:A").Clear
    For i = 1 To LastRow
        For j = 2 To LastColumn(i) 'Calls function "LastColumn" to get the last column of each row
            strConcatenate = strConcatenate & FindReplace(ws.Cells(i, j))
        Next j
        ws.Cells(i, 1) = strConcatenate 'This will past the finished string into column [A] in the specific row
        strConcatenate = "" 'blanks the string, so the next string in the next row is fresh
    Next i
End Sub

Function LastColumn(row%) As Integer
    Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet

    LastColumn = ws.Cells(row, ws.Columns.Count).End(xlToLeft).Column
End Function

Function FindReplace(CellValue$)
    Dim strPattern$: strPattern = "[^A-Za-z]+"    'Pattern to only take care of letters
    Dim strReplace$: strReplace = ""    'Replace everything else with blank
    Dim regex As Object
    Set regex = CreateObject("vbscript.regexp")

    With regex
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With
    FindReplace = regex.Replace(CellValue, strReplace) 'RegEx Function replaces the pattern with blank
End Function

例子：