将文本框值插入列中的第一个空单元格中

Question

>我有以下代码可以正常工作。(文本框位于Excel VBA中的"用户窗体"上，并通过单击该用户窗体上的CommandButton1执行。但是谁能帮我把每个文本框的值插入M列的第一个空单元格？这样插入空行。我将在几个工作簿中使用它，因此使用"ActiveSheet"对我来说是最容易的。

Sub UserForm_Initialize()
Me.TextBox1.Value = CStr(ActiveSheet.Range("M1").Value)
Me.TextBox2.Value = CStr(ActiveSheet.Range("M2").Value)
Me.TextBox3.Value = CStr(ActiveSheet.Range("M3").Value)
Me.TextBox4.Value = CStr(ActiveSheet.Range("M4").Value)
Me.TextBox5.Value = CStr(ActiveSheet.Range("M5").Value)
Me.TextBox6.Value = CStr(ActiveSheet.Range("M6").Value)
Me.TextBox7.Value = CStr(ActiveSheet.Range("M7").Value)
Me.TextBox8.Value = CStr(ActiveSheet.Range("M8").Value)
Me.TextBox9.Value = CStr(ActiveSheet.Range("M9").Value)
Me.TextBox10.Value = CStr(ActiveSheet.Range("M10").Value)
Me.TextBox11.Value = CStr(ActiveSheet.Range("M11").Value)
Me.TextBox12.Value = CStr(ActiveSheet.Range("M12").Value)
Me.TextBox13.Value = CStr(ActiveSheet.Range("M13").Value)
Me.TextBox14.Value = CStr(ActiveSheet.Range("M14").Value)
Me.TextBox15.Value = CStr(ActiveSheet.Range("M15").Value)
Me.TextBox16.Value = CStr(ActiveSheet.Range("M16").Value)
Me.TextBox17.Value = CStr(ActiveSheet.Range("M17").Value)
Me.TextBox18.Value = CStr(ActiveSheet.Range("M18").Value)
Me.TextBox19.Value = CStr(ActiveSheet.Range("M19").Value)
Me.TextBox20.Value = CStr(ActiveSheet.Range("M20").Value)
Me.TextBox21.Value = CStr(ActiveSheet.Range("M21").Value)
Me.TextBox22.Value = CStr(ActiveSheet.Range("M22").Value)
End Sub
'Insert Button
Private Sub CommandButton1_Click()
Range("M1") = Me.TextBox1.Text
Range("M2") = Me.TextBox2.Text
Range("M3") = Me.TextBox3.Text
Range("M4") = Me.TextBox4.Text
Range("M5") = Me.TextBox5.Text
Range("M6") = Me.TextBox6.Text
Range("M7") = Me.TextBox7.Text
Range("M8") = Me.TextBox8.Text
Range("M9") = Me.TextBox9.Text
Range("M10") = Me.TextBox10.Text
Range("M11") = Me.TextBox11.Text
Range("M12") = Me.TextBox12.Text
Range("M13") = Me.TextBox13.Text
Range("M14") = Me.TextBox14.Text
Range("M15") = Me.TextBox15.Text
Range("M16") = Me.TextBox16.Text
Range("M17") = Me.TextBox17.Text
Range("M18") = Me.TextBox18.Text
Range("M19") = Me.TextBox19.Text
Range("M20") = Me.TextBox20.Text
Range("M21") = Me.TextBox21.Text
Range("M22") = Me.TextBox22.Text
Range("A25").Value = "Hide"
Unload Me
End Sub
Sub CommandButton2_Click()
'Cancel button
Unload Me
End Sub

Answer 1

编辑：最后的猜测

你可以做这样的事情：

Private Sub CommandButton1_Click()
Dim i As Long, c As Range
ActiveSheet.Range("M1:M22").ClearContents
Set c = ActiveSheet.Range("M1")
For i = 1 to 22
c.Value = Me.Controls("TextBox" & i).Text
If Len(c.Value) > 0 then Set c = c.offset(1, 0)      
Next i
Unload Me
End Sub