如何复制范围并省略空白单元格？ [英] How to copy a range and omit blank cells?

问题描述

在表1中的列A中，有3000个单元，我需要复制350个单元格。我当前的宏正在复制一切都很好，直到我结束，它复制空白。有没有办法在我的宏中包含一个单元格空白无效代码？

In column A on sheet 1 there are 3000 cells that I need copied at 350 cells each. My current Macro is copying everything just fine until I get to the end and it copies blanks. Is there a way to include a "cell is blank do nothing" code into my macros?

抱歉，如果这听起来没有受过教育，我刚开始学习宏。
这是一个当前宏的副本，宏的其余部分与此相同，只是增加了350个。

Sorry if this sounds uneducated, I'm just starting on learning macro. Here is a copy of the current macro, the rest of the macro is the same as this just with increasing numbers by 350.

Sub Copy_Bins_1_350()
    If Range("D12").Value <> "!" Then
        Exit Sub
    ElseIf Range("D12").Value = "!" Then
        Sheets("sheet1").Select
        Range("B2:B351").Select
        Selection.Copy
        Range("B2").Select
        Sheets("sheet2").Select
        Range("E12").Select
        With Selection.Font
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
        End With
    End If
End Sub

推荐答案

您可以使用 Union 形成您自己的非空单元格范围，然后复制它们。

You can use Union to form your own range of non empty cells and then copy them.

另外有兴趣阅读

尝试这个（ TRIED AND TESTED ）

Sub Sample()
    Dim wsI As Worksheet, wsO As Worksheet
    Dim aCell As Range, rngCopyFrom As Range, rng As Range
    Dim lRow As Long

    Set wsI = ThisWorkbook.Sheets("BIN LIST PASTE")
    Set wsO = ThisWorkbook.Sheets("BIN LIST COPY")

    Set rng = wsI.Range("B2:B351")

    For Each aCell In rng
        If Len(Trim(aCell.Value)) <> 0 Then
            If rngCopyFrom Is Nothing Then
                Set rngCopyFrom = aCell
            Else
                Set rngCopyFrom = Union(rngCopyFrom, aCell)
            End If
        End If
    Next

    If Not rngCopyFrom Is Nothing Then _
    rngCopyFrom.Copy wsO.Range("E12")

    With wsO
        lRow = .Range("E" & .Rows.Count).End(xlUp).Row

        Set rng = .Range("E12:E" & lRow)

        With rng.Font
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
        End With
    End With
End Sub

这篇关于如何复制范围并省略空白单元格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！