Excel VBA - 循环变量
我似乎无法得到我的第二个循环的权利。我正在寻找价值“Persoonlijke prijslijst”的单元格。一旦我有这个单元格,我需要上去两个,然后删除8个单元格。当我调试时,它说temp = 0,所以我认为它在我的第二个循环。Excel VBA - 循环变量
Dim i As Integer
For i = 1 To 800
Range("C" & i).Select
If Range("C" & i).Value = "Persoonlijke prijslijst" Then
Dim temp As Integer
For temp = i - 2 To temp + 8
Range("C" & temp).EntireRow.Delete Shift:=xlToLeft
Next temp
End If
Next i
这是你正在尝试?
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim StrSearch As String
Dim i As Long
'~~> Change this to the relevant sheet name
Set ws = Sheets("Sheet1")
StrSearch = "Persoonlijke prijslijst"
With ws
For i = 800 To 1 Step -1
If .Range("C" & i).Value = StrSearch Then
.Rows(i - 2 & ":" & i + 5).Delete
End If
Next i
End With
End Sub
另一种方法不用循环800次:
Sub testing()
Dim rng As Range
Dim fAddress As String
Dim rngRows As Range
With Sheet1.Range("C1:C800")
Set rng = .Find("Persoonlijke prijslijst")
If Not rng Is Nothing Then
fAddress = rng.Address
Do
If rngRows Is Nothing Then
Set rngRows = rows(rng.Row - 2 & ":" & rng.Row + 5)
Else
Set rngRows = Union(rngRows, rows(rng.Row - 2 & ":" & rng.Row + 5))
End If
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> fAddress
End If
End With
rngRows.EntireRow.Delete
End Sub
凯尔,你在发布前测试了代码吗? ;) – 2012-03-14 15:15:33
这是否适用于多种选择?因此,如果Persoonlijke prijslijst在我的文档中4次? – CustomX 2012-03-14 15:15:49
@Tom,我已经在这个链接中介绍了.Find和.FindNext。 “http://siddharthrout.wordpress.com/2011/07/14/find-and-findnext-in-excel-vba/”对于如此少量的行,可以使用循环。然而,如果你使用'.Find',你必须非常小心,因为如果你删除了范围,那么'Set rng = .FindNext(rng)'会给你错误;)让我知道你是否想要'.Find'代码以及:) – 2012-03-14 15:23:26
你的意思上去的两行,然后从那里删除8行了吗? – 2012-03-14 15:03:58
是的,这是正确的 – CustomX 2012-03-14 15:06:49
这是因为你正在删除行内的第二个循环。每次删除一行时,临时变量也需要更新。但最好是以凯尔的方式去做。 – mattboy 2012-03-14 15:27:18