将最后5列中的任何一列中的空单元格的行复制到其他工作表 [英] Macro to copy a row with an empty cell in any of the last 5 columns to other sheet

问题描述

如上所述，我正在尝试编写一个程序，查看Sheet2中每行的最后5列，如果最后5列中的任何单元格为空，则复制该行，然后将该行粘贴到新表Sheet3。我一直在搜索并找到一些有用的链接，但我不断得到一个需要对象错误。我有一种感觉，我忘了一些简单的东西，但我没有到任何地方。这是我第二天尝试宏，我感谢任何帮助！

  Sub missingDataCopy（）
 
 
 Dim startColumn As Integer 
 Dim startRow As Integer 
 
 Dim totalRows As Integer 
 Dim totalColumns As Integer 
 
 Dim currentColumn As Integer 
 Dim currentRow As Integer 
 
 Dim shouldCopyRow As Boolean 
 
 Dim j As Long 
 
 startColumn = 7 
 totalColumns = 11 
 startRow = 1 
j = 1 
 totalRows = Sheet2.Cells（Rows.Count，startColumn）.End（xlUp）.Row 
 
 
对于currentRow = totalRows To startRow步骤-1 
 shouldCopyRow = False 
 
 
对于currentColumn = startColumn到totalColumns 
如果IsEmpty（Sheet2.Cells（currentRow，currentColumn））然后
 shouldCopyRow = True 
退出
结束如果
下一个
 
如果shouldCopyRow然后
 Sheet2.Cells（currentRow，currentColumn）.EntireRow.Copy目的地：=工作表（嘘eet3）。范围（A& j）
j = j + 1 
结束如果
  

解决方案

您收到的错误表示您的Excel文件中没有 Sheet ，其中 CodeName Sheet 2中。看看下面的例子来看一下工作表（你在标签中看到的）的 Name 与 CodeName ：

如果您愿意要使用 CodeName 代码，那么您必须在VBE中更改它，如上所示。或者，您也可以使用工作表的名称。但是您的行应该显示为：

  totalRows = ThisWorkbook.Worksheets（Sheet1）。单元格（ThisWorkbook.Worksheets（Sheet1 ）.Rows.Count，startColumn）.End（xlUp）.Row 
  

_{因为上述示例中的工作表名称为 Sheet1 。}

As stated above, I'm trying to write a program that looks at the last 5 columns of every row in Sheet2 and copies that row if any of the cells in those last 5 columns are empty, then pastes the row into a new sheet Sheet3. I've been searching and found some helpful links but I keep getting an "Object required" error. I have a feeling I forgot something simple but I'm not getting anywhere. This is my 2nd day attempting macros, I appreciate any help!

Sub missingDataCopy()


Dim startColumn As Integer
Dim startRow As Integer

Dim totalRows As Integer
Dim totalColumns As Integer

Dim currentColumn As Integer
Dim currentRow As Integer

Dim shouldCopyRow As Boolean

Dim j As Long

startColumn = 7
totalColumns = 11
startRow = 1
j = 1
totalRows = Sheet2.Cells(Rows.Count, startColumn).End(xlUp).Row


For currentRow = totalRows To startRow Step -1
    shouldCopyRow = False


    For currentColumn = startColumn To totalColumns
        If IsEmpty(Sheet2.Cells(currentRow, currentColumn)) Then
            shouldCopyRow = True
            Exit For
        End If
    Next

    If shouldCopyRow Then
        Sheet2.Cells(currentRow, currentColumn).EntireRow.Copy Destination:=Worksheets("Sheet3").Range("A" & j)
        j = j + 1
    End If

解决方案

The error you are getting indicates that there is not Sheet in your Excel file with the CodeName Sheet2. Have a look at the following illustration to see the distinction between the Name of a sheet (which you see in the tab) and the CodeName:

If you wish to code with the CodeName then you must change it in the VBE as shown above. Alternatively you can also use the Name of the sheet. But then your line should read:

totalRows = ThisWorkbook.Worksheets("Sheet1").Cells(ThisWorkbook.Worksheets("Sheet1").Rows.Count, startColumn).End(xlUp).Row

_{Because the name of the sheet in the above example is Sheet1.}

这篇关于将最后5列中的任何一列中的空单元格的行复制到其他工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！