arrays - 如果 AC-AF 列包含空白，则剪切并粘贴行

Question

我想要完成的是:
如果我的整个工作表中 AC-AF 列中的任何单元格为空白，请剪切整行并粘贴到标有“MissingShipping”的新工作表中。
代码应该根据行数进行调整，因为这永远不会相同。
从我看到的示例中，我不明白在哪里插入我想要涉过的单元格范围。
我得到错误

"Method 'Range' of object'_Worksheet'

上线 NewSetup.Range(Cells(Destinationrow, 1), Cells(Destinationrow, lastcolumn)).Select .

Option Explicit
Sub Shipping()
    Dim MissingShipping As Worksheet
    Set MissingShipping = Sheets.Add(After:=Sheets(Sheets.Count))
    MissingShipping.Name = "MissingShipping"
    Dim NewSetup As Worksheet
    Dim lastcolumn As Integer
    Dim Destinationrow As Integer
    Dim lastrow As Long
    Set NewSetup = Worksheets("NKItemBuildInfoResults")
    Set MissingShipping = Worksheets("MissingShipping")
    Destinationrow = 1
    lastcolumn = NewSetup.Range("XFD1").End(xlToLeft).Column
    lastrow = NewSetup.Range("A1048576").End(xlUp).Row
    Dim i As Long
    Dim j As Long
    For i = lastrow To 1 Step -1
        For j = 1 To lastcolumn
            If NewSetup.Cells(i, j).Value = "" Then
                NewSetup.Activate
                NewSetup.Range(Cells(i, 1), Cells(i, lastcolumn)).Cut
                MissingShipping.Activate
                NewSetup.Range(Cells(Destinationrow, 1), Cells(Destinationrow, _
                  lastcolumn)).Select
                ActiveSheet.Paste
                NewSetup.Rows(i).Delete shift:=xlUp
                Destinationrow = Destinationrow + 1
                Exit For
            End If
        Next j
    Next i
End Sub

Answer 1

G'day Nikki，

欢迎来到 VBA 的世界!互联网上有很多很棒的资源可以帮助您完成旅程。

在代码中使用范围通常更容易和更快，而不是读取和写入工作表并选择单元格来模仿如果您手动完成工作通常会做的事情。

尽早了解范围对象是个好主意。处理多个工作表很方便。

以下是 Excel 中范围的良好开端:

https://excelmacromastery.com/excel-vba-range-cells/

另一个方便的东西是集合。如果您必须存储一堆东西以供以后使用，您可以将它们添加到集合中，然后使用“For Each”循环遍历它们。这是对集合的一个很好的解释:

https://excelmacromastery.com/excel-vba-collections/

我快速查看了您的代码并使用范围和集合的概念，我已经对其进行了更改，以完成我认为您正在尝试做的事情。我不得不做出一些假设，因为我还没有看到你的表格。我在计算机上的一堆随机行上运行代码以确保它有效。考虑以下:

Dim MissingShipping As Worksheet
Dim NewSetup As Worksheet

Dim rangeToCheck As Range
Dim cellsToCheck As Range
Dim targetRange As Range
Dim rw As Range 'rw is a row
Dim cl As Range 'cl is a cell

Dim rowColl As New Collection

Dim i As Long

Set NewSetup = Worksheets("NKItemBuildInfoResults")
Set MissingShipping = Worksheets("MissingShipping")

'Get the range of data to check
Set rangeToCheck = NewSetup.Range("A1").CurrentRegion

'For each row in the range
For Each rw In rangeToCheck.Rows

    'For the last four cells in that row
    Set cellsToCheck = rw.Cells(1, 29).Resize(1, 4)

    For Each cl In cellsToCheck.Cells

        'If the cell is empty
        If cl.Value = "" Then

            'Add the row to our collection of rows
            rowColl.Add rw

            'Exit the for loop because we only want to add the row once.
            'There may be multiple empty cells.
            Exit For

        End If

    'Check the next cell
    Next cl

Next rw

'Now we have a collection of rows that meet the requirements that you were after

'Using the size collection of rows we made, we now know the size of the range 
'we need to store the values
'We can set the size of the new range using rowColl.Count 
'(that's the number of rows we have)
Set targetRange = MissingShipping.Range("A1").Resize(rowColl.Count, 32)

'Use i to step through the rows of our new range
i = 1

'For each row in our collection of rows
For Each rw In rowColl

    'Use i to set the correct row in our target range an make it's value 
    'equal to the row we're looking at
    targetRange.Rows(i) = rw.Value

    'Increment i for next time
    i = i + 1

Next rw

End Sub

祝你好运!希望这可以帮助。