查找并删除单元格值为“#N/A"的行. [英] Find and delete Rows where cell value is "#N/A"
本文介绍了查找并删除单元格值为“#N/A"的行.的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个excel文档,用于分析数据集,我带入的每个数据资产都有不同数量的数据.我尝试编写一个分配给按钮的宏,该宏可以根据单元格的值识别删除行.这没用.我在做什么错了?
I have an excel document that I use to analyze data sets each data asset I bring in has varying amounts of data. I have tried to write a macro that I assign to a button that can identify delete rows based on the value of a cell. It does not work. What am I doing wrong?
Sub Button2_Click()
[vb]
'This will find how many rows there are
With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
MsgBox lastRow
End With
Sub sbDelete_Rows_Based_On_Criteria()
Dim lRow As Long
Dim iCntr As Long
lRow = lastRow
For iCntr = lRow To 1 Step -1
'Replaces XX with the variable you want to delete
If Cells(iCntr, 1) = "#N/A" Then
Rows(iCntr).Delete
End If
Next
End Sub
[/vb]
End Sub
推荐答案
您的逻辑已基本存在,但您的语法已关闭.另外,您只检查A列的值,而不检查B列(根据上面的评论).
Your logic is pretty much there, but your syntax is off. Additionally, you are only checking column A for the value and not column B (per your comments above).
Sub Button2_Click()
Dim lRow As Long
'This will find how many rows there are
With ActiveSheet
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
MsgBox lastRow
End With
Dim iCntr As Long
For iCntr = lRow To 1 Step -1
'Replace "#N/A" with the value you want to delete
' Check column A and B for the value.
If Cells(iCntr, 1).Text = "#N/A" Or Cells(iCntr, 2).Text = "#N/A" Then
Rows(iCntr).Delete
End If
Next
End Sub
或简化:
Sub Button2_Click()
Dim iCntr As Long
For iCntr = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
'Replace "#N/A" with the value you want to delete
' Check column A and B for the value.
If Cells(iCntr, 1).Text = "#N/A" Or Cells(iCntr, 2).Text = "#N/A" Then
Rows(iCntr).Delete
End If
Next
End Sub
这篇关于查找并删除单元格值为“#N/A"的行.的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文