Excel表达式复制行，但删除空白行 [英] Excel expression to copy rows but remove blank rows

问题描述

我需要将数据从一个工作表复制到另一个工作表。但是，我需要一个条件复制操作，它将基于条件跳过行。

例如，如果我开始...

 活动价值
是1 
否2 
否3 
是4 
否5 
 no 6 
  

我只想复制Active = yes的行，所以我最终会...

 价值
 1 
 4 
  / pre> 
 
 
有人可以告诉我如何使用1）一个宏和2）一个公式？
解决方案
 公式方法： 
 
 
 
假设您的数据位于sheet1，范围 A2：B7 。
 
 
 
然后在sheet2单元格中使用此公式 A2  p> 
 
 
  = IFERROR（INDEX（Sheet1！B：B，SMALL（IF（Sheet1！$ A $ 2：$ A $ 7 =yes ，ROW（Sheet1！$ A $ 2：$ A $ 7）），ROW（） -  ROW（$ A $ 2）+1）），）
  
与数组条目（ CTRL  +  SHIFT  +  ENTER ）和th将其拖动下来。
 
 
 
  
 
 
 
  VBA方法 
 
 
 
您可以使用 AutoFilter ：
  Sub test（）
 Dim ws1 As Worksheet，ws2 As Worksheet 
 Dim rng As Range，rngToCopy As Range 
 Dim lastrow As Long 
'更改Sheet1和Sheet2以适合
设置ws1 = ThisWorkbook.Worksheets（Sheet1）
设置ws2 = ThisWorkbook.Worksheets（Sheet2）
 
带ws1 
'假设您的数据存储在列A：B，Sheet1 
 lastrow = .Cells（.Rows.Count，A）。End（xlUp）.Row 
设置rng = .Range（A1：B& 
'清除所有过滤器
 .AutoFilterMode = False 
带rng 
'应用过滤器
 .AutoFilter字段：= 1，条件1：=是
 On Error Resume Next 
'只显示行
设置rngToCopy = .SpecialCells（xlCellTypeVisible）
错误GoTo 0 
结束
'复制范围
如果不是rngToCopy是没有，然后rngToCopy.Copy目的地：= ws2.Range（A1）
'清除所有过滤器
 .AutoFilterMode = False 
结束与
应用程序。 CutCopyMode = False 
 End Sub 
  
  
 
 
 
请注意，如果您只想复制 code>列，更改
 设置rngToCopy = .SpecialCells（xlCellTypeVisible）
  
到
 设置rngT oCopy = .Offset（，1）.Resize（，.Columns.Count  -  1）.SpecialCells（xlCellTypeVisible）
  
 
I need to copy data from one worksheet into another.  However, I need a conditional copy operation that will skip rows based on criteria.


For example, if I start with...
Active  Value
yes     1
no      2
no      3
yes     4
no      5
no      6
I only want to copy rows that are Active=yes, so I would end up with...
Value
1
4
Can someone show me how this is done with 1) a macro and 2) a formula?
解决方案
Formula approach:

suppose your data are in sheet1, range A2:B7.

Then use this formula in sheet2 cell A2:
=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$7="yes",ROW(Sheet1!$A$2:$A$7)),ROW()-ROW($A$2)+1)),"")
with array entry (CTRL+SHIFT+ENTER) and then drag it down.



VBA approach:

You can use AutoFilter:
Sub test()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rng As Range, rngToCopy As Range
    Dim lastrow As Long
    'change Sheet1 and Sheet2 to suit
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")

    With ws1
        'assumung that your data stored in column A:B, Sheet1
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng = .Range("A1:B" & lastrow)
        'clear all filters
        .AutoFilterMode = False
        With rng
            'apply filter
            .AutoFilter Field:=1, Criteria1:="yes"
            On Error Resume Next
            'get only visible rows
            Set rngToCopy = .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
        End With
        'copy range
        If Not rngToCopy Is Nothing Then rngToCopy.Copy Destination:=ws2.Range("A1")
        'clear all filters
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
End Sub


Note, if you want to copy only Value column, change
Set rngToCopy = .SpecialCells(xlCellTypeVisible)
to
Set rngToCopy = .Offset(, 1).Resize(, .Columns.Count - 1).SpecialCells(xlCellTypeVisible)


                        
这篇关于Excel表达式复制行，但删除空白行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！