如何避免使用选择并在进入列的底部时进行选择

Question

我试图停止使用ActiveCell等

我当前的代码是：

Sub SitesAndProd()
Set wb = ActiveWorkbook
Set ws = Worksheets("Data")
Set rng = ws.Cells(1, 13)
    LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    rng.FormulaR1C1 = "SitesAndProd"                                              'Rename Cell SitesAndProd
Set rng = ws.Cells(2, 13)
    rng.FormulaR1C1 = "=RC[-12]&RC[-4]"
    rng.Offset(0, -1).Select                                                     'Move left 1 column
    Selection.End(xlDown).Select                                                        'Go to bottom of column
    rng.Offset(0, 1).Select                                                      'Move right 1 column
    Range(Selection, Selection.End(xlUp)).Select                                        'Go to top of Column
    Selection.FillDown                                                                  'Copy Formula Down "Fill"
    Selection.Copy                                                                      'Ctrl + C
    Selection.PasteSpecial xlPasteValues                                                'Right click + V
    Application.CutCopyMode = False                                                     'Esc (stops the crawling ants

End Sub

稍后使用Selection.End(xlDown).Select和xlUp时 - 它不能保存范围位置

确保范围保留在此处的最佳方法是什么？

使用以下内容：

Range("M2").Select
ActiveCell.FormulaR1C1 = "=RC[-12]&RC[-4]"
Range("M2").Select
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown

代码将拉动正确的表格 - 向左1，底部为右1，选择为to，然后沿

任何机会都可以将我指向正确的方向，而无需ActiveCell，Selection和Select？

Answer 1

这是您在A列上计算的Lastrow等于列M

中相同数量的行

Option Explicit
Sub SitesAndProd()
    Dim wb As Workbook, ws As Worksheet, LastRow As Long
    Set wb = ThisWorkbook
    Set ws = Worksheets("Data")
    ws.Cells(1, 13) = "SitesAndProd"
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    With ws.Range(ws.Cells(2, 13), ws.Cells(LastRow, 13))
        .FormulaR1C1 = "=RC[-12]&RC[-4]"
        .Value = .Value
    End With
End Sub

我已经调整了一些代码。您需要声明您的变量wb As Workbook和ws As Worksheet。如果您收到代码的工作簿，请使用ThisWorkbook而不是ActiveWorkbook，您将获得更少的错误。

编辑：尝试避免尽可能多的全局变量。将它们传递给您的subs或函数作为变量。

Answer 2

我希望您的代码：

Range("M2").Select
ActiveCell.FormulaR1C1 = "=RC[-12]&RC[-4]"
Range("M2").Select
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown

可以替换为：

Range(Range("M2"), Range("M" & ActiveSheet.Rows.Count).End(xlUp)).Formula = "=RC[-12]&RC[-4]"

如果您想用来确定最后一个填充单元的列为列Q：

Range(Range("M2"), Range("Q" & ActiveSheet.Rows.Count).End(xlUp).Row).Formula = "=RC[-12]&RC[-4]"