Excel宏连接 [英] Excel Macro to concatenate

问题描述

需要帮助创建一个Excel宏。我有一个Excel表格。Excel表不一致。
我打算使其统一和结构化。

Need help in creating an Excel Macro.I have an Excel sheet.The Excel sheet is not consistent. I am planning to make it uniform and structured.

例如

  A            B            C         D
1 test      tester         tester
2 hai       test
3 Bye       test           tested
4 GN        test           tested    Fine

  A            B            C         D
1 test      testertester   
2 hai       test
3 Bye       testtested     
4 GN        testtestedFine 

基本上，我必须找到最后一个单元格放置元素，所以基于我可以写我的CONCATENATE函数。

Basically I have to find the last cell where element is placed so based on that I can write my CONCATENATE funciton.

在这种情况下，它将是列D因此我的连接功能将是
= CONCATENATE（B1，C1，D1）
我再次希望结果在B1中，但如果我必须隐藏，则不会出现问题。

In this case it would be Column D and hence my concatenate function would have been =CONCATENATE(B1,C1,D1) Again I would like the result to be in B1 but not a problem if I have to hide.

任何人都可以帮助我这样做？

Can anyone help me in doing this?

推荐答案

您可以使用以下VBA功能连接（连接）t他从单元格的任意范围中选择一个可选的分隔符。

You could use the following VBA function which joins (concatenates) the values from an arbitrary range of cells, with an optional delimiter.

Public Function Join(source As Range, Optional delimiter As String)
    Dim text As String
    Dim cell As Range: For Each cell In source.Cells
        If cell.Value = "" Then GoTo nextCell

        text = text & cell.Value & delimiter

nextCell:
    Next cell

    If text <> "" And delimiter <> "" Then
        text = Mid(text, 1, Len(text) - Len(delimiter))
    End If

    Join = text
End Function

有关如何使用该功能的示例，请将= JOIN（A1：D1）输入到任何位置的单元格中电子表格。

For an example of how to use the function, enter =JOIN(A1:D1) into a cell anywhere on the spreadsheet.

这篇关于Excel宏连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！