从引用列中选择最后一个填充的单元格

Question

我正试图找到最后一个空列并写入列名。代码通过名称找到这个新列，然后选择第二个单元格，并将列B和列C中的值粘贴到exceldown。但是，代码用连字符"填充D列的所有空白单元格-&"；。我想以某种方式定义最后填充的单元格，并只粘贴到B列的最后填充单元格。我面临语法错误。任何线索我如何实现？非常感谢。

片材">水果蔬菜"；

C水果蔬菜苹果橙色

	A	B
1	类别
2	空白	菠菜
3	空白	卷心菜

没有测试，但应该可以工作-我刚刚修改了公式行。您需要通过从工作表底部向上查找最后一行，而无需选择任何内容。

Sub Merge_FV()

Dim r1 As Range, r2 As Range, r3 As Range
Dim emptyColumn As Long, LastRow As Long
'find empty Column (actually cell in Row 1)'
emptyColumn = Cells(1, Columns.Count).End(xlToLeft).Column
If emptyColumn > 1 Then
emptyColumn = emptyColumn + 1
End If
Cells(1, emptyColumn).Value = "FruitsVege"
With Rows(1)
Set r1 = .Find(What:="Fruits", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
Set r2 = .Find(What:="Vegetables", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
Set r3 = .Find(What:="FruitsVege", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not r1 Is Nothing And Not r2 Is Nothing And Not r3 Is Nothing Then
LastRow = Cells(Rows.Count, r1.Column).End(xlUp).Row
r3.Offset(1, 0).Resize(LastRow - 1).Formula = "=" & r1.Offset(1).Address(0, 0) & " & ""-"" & " & r2.Offset(1).Address(0, 0)
End If
End With
End Sub