如何自动填充活动单元格的列直到最后一行 [英] How to auto fill the column of the active cell till the last row

问题描述

尝试执行以下代码:

ActiveCell.Offset(1, 0).Select 'Noting but F2





  ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)"





' find last row with data in column "A"

lastRow = Cells(Rows.Count, "A").End(xlUp).Row



' copying the formula from "F2" all the way to the last cell

ActiveCell.AutoFill Destination:=Range(ActiveCell.EntireColumn & lastRow), Type:=xlFillDefault

给出错误信息，提示类型不匹配.

It gives an error saying type mismatch.

但是当我尝试:

ActiveCell.AutoFill Destination:=Range("F2:F" & lastRow), Type:=xlFillDefault

它可以正常工作，但是我不希望F2:F出现，因为我不知道将来会出现哪一列.

It works properly, but I don't want F2:F there, as I don't know which column will be in future.

推荐答案

要回答您的问题，您未指定正确的范围.正如您所说，可以指定一个简单的范围

To answer your question you are not specifying a right range. A simple range can be, as you said, specified

1. 使用字符串格式 Range("A1:B4")，其中 A1 和 B4 是范围的两个对角.

1. Using the string format Range("A1:B4") where A1 and B4 are two opposite corners of the range.

使用两个角作为单元格，例如 Range(Cells(1,1 ,, Cells(4,2))，其中 Cells(1,1)是 A1 和Cells(4,2)是 B4

Using the two corners as cells, e.g. Range( Cells(1,1), Cells(4,2) ) where Cells(1,1) is A1 and Cells(4,2) is B4

所以你可以写

ActiveCell.AutoFill Destination:=Range(Cells(2, ActiveCell.Column), Cells(lastRow, ActiveCell.Column)), Type:=xlFillDefault

要达到目标，您还可以避免使用自动填充.FormulaR1C1格式允许您编写具有相对"单元格偏移量/地址的公式.因此，自动填充后，desiderd列中的所有单元格将具有相同的FormulaR1C1.然后，您可以避免使用自动填充功能，并为整个列手动设置相同的FormulaR1C1.

To reach your goal you could also avoid using the AutoFill. FormulaR1C1 format allow you to write formula with 'relative' cell offsets/address. So after the AutoFill all the cells in the desiderd column will have the same FormulaR1C1. Then you can avoid using the AutoFill and manually set the same FormulaR1C1 for the whole column.

因此，您可以编写像这样的简单代码:

So you can write a simpler code like that:

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(1, ActiveCell.Column), Cells(lastRow, ActiveCell.Column)).FormulaR1C1 = "=LEFT(RC[-1],4)"

这篇关于如何自动填充活动单元格的列直到最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！