Excel表达式复制行,但删除空白行 [英] Excel expression to copy rows but remove blank rows
问题描述
我需要将数据从一个工作表复制到另一个工作表。但是,我需要一个条件复制操作,它将基于条件跳过行。
例如,如果我开始...
活动价值
是1
否2
否3
是4
否5
no 6
我只想复制Active = yes的行,所以我最终会...
价值
/ pre>
1
4
有人可以告诉我如何使用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, changeSet rngToCopy = .SpecialCells(xlCellTypeVisible)
to
Set rngToCopy = .Offset(, 1).Resize(, .Columns.Count - 1).SpecialCells(xlCellTypeVisible)
这篇关于Excel表达式复制行,但删除空白行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!