如何在VBA中使范围动态

Question

下面，我有发送个性化短信的代码并包含名称。我让那部分工作了。现在，我只需要使我的范围是动态的，并在相应的列中向下移动，直到没有人可以留言。在当前状态下，它只会向第一人发送消息。我尝试查找动态范围教程、循环等，但它们要么太复杂，我无法掌握，要么需要我重写我已经工作的内容。

Private Sub btnSend_Click()

   Dim contactNumberRange As Range
   Dim messageRange As Range
   Dim clientNameRange As Range
   Dim phoneCell As Range
   Dim messageCell As Range
   Dim nameCell As Range

   Set contactNumberRange = Range("D2") //Need to make this range dynamic
   Set messageRange = Range("E2") //This too
   Set clientNameRange = Range("A2") //This aswell

   For Each phoneCell In contactNumberRange
    For Each messageCell In messageRange
     For Each nameCell In clientNameRange
      SendMessage FROMPHONE, nameCell.Value, phoneCell.Value, messageCell.Value
     Next
    Next
   Next


 Me.Hide
End Sub

Answer 1

您只需要使用最后一行变量来修正范围。

此外，使用工作表限定这些范围！

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim LR As Long
LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
Set contactNumberRange = ws.Range("D2:D" & LR)
Set messageRange = ws.Range("E2:E" & LR)
Set clientNameRange = ws.Range("A2:A" & LR)
For Each phoneCell In contactNumberRange
    For Each messageCell In messageRange
        For Each nameCell In clientNameRange
            SendMessage FROMPHONE, nameCell.Value, phoneCell.Value, messageCell.Value
        Next nameCell
    Next messageCell
Next phoneCell