vba 宏自动填充到最后一个相邻单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18488162/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Macro to Auto Fill Down to last adjacent cell
提问by user2725363
I am trying to use macro recorder in Excel to record a macro to fill down a column of cells, however because the fill down each time is a different number of cells it either fills down to short or too long and this seems to be because the macro identifies the cell range and its fixed.
我试图在 Excel 中使用宏记录器来记录一个宏来填充一列单元格，但是因为每次填充是不同数量的单元格，它要么填充太短要么太长，这似乎是因为宏标识单元格范围及其固定。
Is there anyway that I can get it to fill down to the last populated neighbouring cell. E.g. AutoFill down column E until it reaches the last populated row in column D. I have looked at some examples on here but the code all looks very different so not sure if it can be done with macro recorder or I have to get someone to write some code or is it something that has to be done manually?
无论如何我可以让它填充到最后一个填充的相邻单元格。例如，自动填充 E 列，直到它到达 D 列中最后填充的行。我在这里查看了一些示例，但代码看起来都非常不同，所以不确定是否可以使用宏记录器完成，或者我必须让某人编写一些代码还是必须手动完成？
This is the code that I have in the macro.
这是我在宏中的代码。
ActiveCell.FormulaR1C1 = _
"=IF(MONTH(RC[-1])>3,"" ""&YEAR(RC[-1])&""-""&RIGHT(YEAR(RC[-1])+1,2),"" ""&YEAR(RC[-1])-1&""-""&RIGHT(YEAR(RC[-1]),2))"
Selection.AutoFill Destination:=Range("E2:E1344")
'Selection.AutoFill Destination:=Range("E2:E1344")
Range("E2:E1344").Select
If anyone can help i'd be extremely grateful
如果有人可以提供帮助，我将不胜感激
回答by Vasim
Untested....but should work.
未经测试....但应该工作。
Dim lastrow as long
lastrow = range("D65000").end(xlup).Row
ActiveCell.FormulaR1C1 = _
"=IF(MONTH(RC[-1])>3,"" ""&YEAR(RC[-1])&""-""&RIGHT(YEAR(RC[-1])+1,2),"" ""&YEAR(RC[-1])-1&""-""&RIGHT(YEAR(RC[-1]),2))"
Selection.AutoFill Destination:=Range("E2:E" & lastrow)
'Selection.AutoFill Destination:=Range("E2:E"& lastrow)
Range("E2:E1344").Select
Only exception being are you sure your Autofill code is perfect...
唯一的例外是您确定您的自动填充代码是完美的...
回答by xQbert
This example shows you how to fill column B based on the the volume of data in Column A. Adjust "A1" accordingly to your needs. It will fill in column B based on the formula in B1.
此示例向您展示如何根据 A 列中的数据量填充 B 列。根据您的需要调整“A1”。它将根据 B1 中的公式填写 B 列。
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
回答by chris gagnon
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
