仅使用可见单元格连接

Question

我希望将一张纸上的许多单元格连接到另一张纸上，然后将该公式复制到最后一行。

但是，我要连接的工作表具有隐藏的单元格。所以目前这弄乱了我在目标工作表上的结果。

有没有办法解决这个问题，让它只拾取可见细胞？

Dim FitRng As Range, Lastrowteam As Long
Lastrowteam = Cells(Rows.Count, "H").End(xlUp).Row
Sheets("Pipeline simplified").Select
Range("W7").FormulaR1C1 = _
"=CONCATENATE(Pipeline!R[2]C[7],"" "",Pipeline!R[2]C[8],"" "",Pipeline!R[2]C[9],"" "",Pipeline!R[2]C[10])"
Set FitRng = Range("W7:W" & Lastrowteam).SpecialCells(xlCellTypeVisible)
FitRng.FillDown

Answer 1

根据问题中提供的代码，这样的东西应该适合您：

Sub tgr()
    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim wsDest As Worksheet
    Dim rData As Range
    Dim rDest As Range
    Dim DataCell As Range
    Dim aResults() As String
    Dim i As Long
    Set wb = ActiveWorkbook
    Set wsData = wb.Worksheets("Pipeline Simplified")   'Sheet where you are getting the concatenated data
    Set wsDest = wb.Worksheets("Sheet1")                'Sheet where the results will be output
    Set rDest = wsDest.Range("A2")                      'Cell on destination sheet where output results will start
    On Error Resume Next
    Set rData = wsData.Range("G2", wsData.Cells(wsData.Rows.Count, "G").End(xlUp)).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If rData Is Nothing Then Exit Sub   'No visible cells
    If rData.Row < 2 Then Exit Sub      'No data
    ReDim aResults(1 To rData.Cells.Count, 1 To 1)
    For Each DataCell In rData.Cells
        i = i + 1
        aResults(i, 1) = DataCell.Value & " " & DataCell.Offset(, 1).Value & " " & DataCell.Offset(, 2).Value & " " & DataCell.Offset(, 3).Value
    Next DataCell
    rDest.Resize(UBound(aResults, 1)).Value = aResults
End Sub