I have written a small script in VBA which checks the value of a cell in a given range against a list. If the cell values match a value in the list it is kept, else it is deleted. I was wondering how I could make it run backward, as running it forwards creates issues. I have researched this somewhat and I have tried appending 'Step -1' to the end of the line which begins the for loop, but this doesn't work in this case.
我在VBA中编写了一个小脚本,它根据列表检查给定范围内的单元格的值。如果单元格值与列表中的值匹配则保留,否则将删除。我想知道如何让它向后运行,因为向前运行会产生问题。我已经对此进行了一些研究,并且我尝试将“Step -1”附加到开始for循环的行的末尾,但是在这种情况下这不起作用。
Set Rng = Range("A9:V9")
For Each cell In Rng
If Not myList.Exists(cell.Value) Then
cell.EntireColumn.Delete
End If
Next
2 个解决方案
#1
6
In this case, probably some for-loop like this one would be enough:
在这种情况下,可能像这样的一些for循环就足够了:
Option Explicit
Sub TestMe()
Dim rng As Range
Dim cnt As Long
Set rng = Range("A9:V9")
For cnt = rng.Cells.Count To 1 Step -1
Cells(rng.Row, cnt) = 23
Stop
Next
End Sub
I have put Stop
so you can see which cell is referred. Once you hit the Stop
, continue further with F5.
我已经把Stop设置为可以看到哪个单元格被引用。一旦你点击停止,继续使用F5。
#2
0
Richard, you're entirely right that the "Step -1" approach would be the right solution in this case. You simply have to change the variable references around to work with the loop.
理查德,你完全正确,在这种情况下,“Step -1”方法将是正确的解决方案。您只需更改变量引用即可使用循环。
For example:
例如:
Set Rng = Range("A9:V9")
For i = rng.rows.count to 1 step -1
for j = rng.columns.count to 1 step -1
if not myList.Exists(rng.cells(i, j).value) then
rng.cells(i, j).entirecolumn.delete ' This probably won't work, but you get the idea.
end if
next j
next i
#1
6
In this case, probably some for-loop like this one would be enough:
在这种情况下,可能像这样的一些for循环就足够了:
Option Explicit
Sub TestMe()
Dim rng As Range
Dim cnt As Long
Set rng = Range("A9:V9")
For cnt = rng.Cells.Count To 1 Step -1
Cells(rng.Row, cnt) = 23
Stop
Next
End Sub
I have put Stop
so you can see which cell is referred. Once you hit the Stop
, continue further with F5.
我已经把Stop设置为可以看到哪个单元格被引用。一旦你点击停止,继续使用F5。
#2
0
Richard, you're entirely right that the "Step -1" approach would be the right solution in this case. You simply have to change the variable references around to work with the loop.
理查德,你完全正确,在这种情况下,“Step -1”方法将是正确的解决方案。您只需更改变量引用即可使用循环。
For example:
例如:
Set Rng = Range("A9:V9")
For i = rng.rows.count to 1 step -1
for j = rng.columns.count to 1 step -1
if not myList.Exists(rng.cells(i, j).value) then
rng.cells(i, j).entirecolumn.delete ' This probably won't work, but you get the idea.
end if
next j
next i