比较单元格以删除行,值为true但不删除行 [英] Compare cells to delete rows, value is true but not deleting rows
本文介绍了比较单元格以删除行,值为true但不删除行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
第一个单元格是使用1页中的公式导出的,另一个是仅数字,是否有所区别?
Dim r,s,i,t As Long
Dim com,cc,bl,acc As Long
Dim rDB,rInput As Range
Dim shDB,shInput As Worksheet
设置shDB = ActiveWorkbook.Sheets(数据库)
设置rDB = shDB.Range(A1,T1000)
设置shInput = ActiveWorkbook.Sheets输入)
设置rInput = shInput.Range(A1,R1000)
r = 2
Do While Len(shDB.Cells(r,1).Formula )> 0
com = shInput.Cells(7,5).Value
cc = shInput.Cells(5,5).Value
bl = shInput.Cells(9,5).Value
acc = shInput.Cells(5,10).Value
MsgBox(com&& shDB.Cells(r,1).Value&&& ; rDB.Cells(r,2).Value&& rDB.Cells(r,3).Value&& bl&& rDB.Cells(r,4) && acc)
如果shDB.Cells(r,1).Value = com和rDB.Cells(r,2).Value = cc和rDB.Cells(r,3).Value = bl和rDB.Cells(r,4).Value = acc然后
shDB.Rows(r).EntireRow.Delete
MsgBox(删除行)
Else
r = r + 1
结束如果
循环
解决方案
当删除从最后一个索引到第一个索引。这适用于列表框,组合框,范围,...等。
如果从头到尾删除,那么您将跳过每一行
I'm trying to compare 2 cells and if its true that row will be deleted, i tried using msgbox to return the value and it shows its true, but row is not deleting. The first cell is derived using formula in 1 sheet and the other is just numbers, does that make a difference?
Dim r, s, i, t As Long
Dim com, cc, bl, acc As Long
Dim rDB, rInput As Range
Dim shDB, shInput As Worksheet
Set shDB = ActiveWorkbook.Sheets("Database")
Set rDB = shDB.Range("A1", "T1000")
Set shInput = ActiveWorkbook.Sheets("Data Input")
Set rInput = shInput.Range("A1", "R1000")
r = 2
Do While Len(shDB.Cells(r, 1).Formula) > 0
com = shInput.Cells(7, 5).Value
cc = shInput.Cells(5, 5).Value
bl = shInput.Cells(9, 5).Value
acc = shInput.Cells(5, 10).Value
MsgBox (com & " " & shDB.Cells(r, 1).Value & " " & cc & " " & rDB.Cells(r, 2).Value & " " & rDB.Cells(r, 3).Value & " " & bl & " " & rDB.Cells(r, 4).Value & " " & acc)
If shDB.Cells(r, 1).Value = com And rDB.Cells(r, 2).Value = cc And rDB.Cells(r, 3).Value = bl And rDB.Cells(r, 4).Value = acc Then
shDB.Rows(r).EntireRow.Delete
MsgBox ("deleting rows")
Else
r = r + 1
End If
Loop
解决方案
When deleting alway go from the last index to the first. This applies to listboxes, comboboxes, ranges, ...etc.
If you delete from first to last then you will skip every other row
这篇关于比较单元格以删除行,值为true但不删除行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文