vba 如果单元格有文本，则复制一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28196303/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy a Row if a Cell has Text
提问by user3514907
I am trying to write a macro that copies a row if a particular cell in that row contains text (Column C), I have searched the internet and have found ones that deal with copying rows with specific text but I want to write one that copies it regardless of what text is contained in the cell.
我正在尝试编写一个宏，如果该行中的特定单元格包含文本（C 列），则该宏会复制该行，我在互联网上进行了搜索，并找到了处理带有特定文本的行复制的宏，但我想编写一个复制行它与单元格中包含什么文本无关。
For example if there are 30 rows but only 10 contain text in column C I want it to copy those 10 rows and paste them into a 'Action Summary' sheet.
例如，如果有 30 行但只有 10 行包含 CI 列中的文本，则希望它复制这 10 行并将它们粘贴到“操作摘要”表中。
At the moment I am using a conditional format to colour any cells without text and then filtering them out and copying the remaining rows but this is a little messy and I wondered if there was a cleaner way of doing it.
目前我正在使用条件格式为没有文本的任何单元格着色，然后将它们过滤掉并复制剩余的行，但这有点混乱，我想知道是否有更简洁的方法来做到这一点。
Thank you.
谢谢你。
回答by Gary's Student
Perhaps:
也许：
Sub SmartCopy()
Dim s1 As Worksheet, s2 As Worksheet
Dim N As Long, i As Long, j As Long
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Action Summary")
N = s1.Cells(Rows.Count, "C").End(xlUp).Row
j = 1
For i = 1 To N
If s1.Cells(i, "C").Value = "" Then
Else
s1.Cells(i, "C").EntireRow.Copy s2.Cells(j, 1)
j = j + 1
End If
Next i
End Sub
Update the Sheetnames to match your requirements.
更新工作表名称以符合您的要求。
回答by user3479671
Something like this then:
然后是这样的：
Dim y As Integer
Dim y 作为整数
y = 1
For x = 1 To 30
If Sheets(1).Cells(x, 3) <> "" Then
Do While Sheets("Action Summary").Cells(y, 3) <> ""
y = y + 1
Loop
Sheets(1).Rows(x).Copy Destination:=Sheets("Action Summary").Rows(y)
y = 1
End If
Next x
it loops though the first 30 lines of sheet one, then upon finding a value in column c (which is column 3) it loops through the c column values of the "Action Summary" sheet incrementing the y variable until it finds a blank row to paste in, then it copies the entire row x of sheet 1 into the row y of the "Action Summary" sheet.
它循环第一个表的前 30 行，然后在 c 列（即第 3 列）中找到一个值时，它循环遍历“操作摘要”表的 c 列值，递增 y 变量，直到找到一个空白行粘贴进去，然后将工作表 1 的整行 x 复制到“操作摘要”工作表的行 y 中。
回答by Jeanno
Try this
尝试这个
Private Sub CopyNonBlank()
Dim destWS As Worksheet ' Destination worksheet
Set destWS = ThisWorkbook.Worksheets("Action Summary")
Dim i As Long: i = 1
For Each cell In Range("C2:C32")
If Len(cell.Value) > 0 Then ' if cell is not empty lenght would be greater than 0
destWS.Range("A" & i).Value = cell.Value
i = i + 1
End If
Next cell
End Sub
回答by Matt_Roberts
You can use an If statement to determine if the cell contains text using something like:
您可以使用 If 语句来确定单元格是否包含使用以下内容的文本：
If IsNumeric(rCell.Value) = False And _
IsError(rCell.Value) = False And _
IsDate(rCell.Value) = False Then
1st line checks the value is not a number, the next checks it is not an error message from a formula and the last checks it is not a date as these can be mistaken for text depending how they are formatted.
第一行检查该值不是数字，接下来检查它不是来自公式的错误消息，最后一次检查它不是日期，因为这些可能被误认为是文本，具体取决于它们的格式。
Then add your code to copy the row that you have already found.
然后添加您的代码以复制您已经找到的行。
