选择具有相同 ID 号的所有行，然后迭代 - VBA

Question

我在Excel中有一个排序的ID列表。我想抓取具有相同ID的所有行，然后我将操纵它们。

Sub Selectingabox()
Dim I As Integer
Dim N As Integer
'Defining
I = 1
N = 1
'Initializing
While I < 3000
'If I have more than 3k rows I'm in serious trouble anyways
If Range("A" & I).Select = Range("A" & I + 1).Select Then
 I = I + 1
Else: Range("A" & N, "AJ" & I).Select
'Lots of stuff manipulating the data range we just selected
N = I + 1
'The new top row
I = I + 1
'The new bottom row

Wend
End Sub

不太管用......文德对我不满意，我不知道为什么。也不知道代码是否有效！

Answer 1

几件事：

• 将值调暗为长而不是整数，这样就不会在第 32k 行出现错误
• 使用 FOR 循环而不是 while 循环来提高代码效率
• 您可以使用VBA自动查找最后一行，而不是使用数字3000 Cells(Rows.Count, "A").End(xlUp).Row

• 我不建议在您的代码中使用.Select。你想操纵什么？

  Sub Selectingabox()
  Dim I As Long
  Dim N As Long
  Dim lastrow As Long
  lastrow = Cells(Rows.Count, "A").End(xlUp).Row
  N = 1
  For I = 1 To lastrow
      If I = lastrow Then
          If Range("A" & I).Value <> Range("A" & I - 1).Value Then
          Range("A" & N & ":AJ" & I).Select
          N = I + 1
          End If
      Else
          If Range("A" & I).Value <> Range("A" & I + 1).Value Then
              Range("A" & N & ":AJ" & I).Select
          'Lots of stuff manipulating the data range we just selected
          N = I + 1
          End If
      End If
  Next I
  End Sub
  

Answer 2

在代码中添加一个End If - 这将解决编译错误

Sub Selectingabox()
Dim I As Integer
Dim N As Integer
'Defining
I = 1
N = 1
'Initializing
While I < 3000
'If I have more than 3k rows I'm in serious trouble anyways
If Range("A" & I).Select = Range("A" & I + 1).Select Then
 I = I + 1
Else: Range("A" & N, "AJ" & I).Select
'Lots of stuff manipulating the data range we just selected
N = I + 1
'The new top row
I = I + 1
'The new bottom row
End If ' add this here
Wend
End Sub