VBA 使用 xlFillSeries 自动填充可见单元格（过滤范围）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9037349/
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
VBA Auto fill visible cells (filtered range) with xlFillSeries
提问by PerfectGamesOnline.com
I want to fill in 2..N integer series into filtered/visible range.
我想将 2..N 整数系列填充到过滤/可见范围内。
This code fills in the series but ignores the filtered range:
此代码填充系列但忽略过滤范围：
COL_SID_CURRENT = 3
COL_SID_CURRENT_STR = "c"
ROW_LAST = ActiveSheet.UsedRange.Rows.Count
' Start the series with value 2 in cell "c2"
Cells(2, COL_SID_CURRENT).FormulaR1C1 = "2"
aRangeStr = "" & COL_SID_CURRENT_STR & "2" ' -> "c2"
Range(aRangeStr).Select
aRangeStr = aRangeStr & ":" & COL_SID_CURRENT_STR & ROW_LAST ' -> "c2:c24"
Selection.AutoFill Destination:=Range(aRangeStr),Type:=xlFillSeries
I've tried to add the xlCellTypeVisibleinto the code but none of my combinations worked:
我试图将 加入xlCellTypeVisible到代码中，但我的组合都不起作用：
Range(aRangeStr).SpecialCells(xlCellTypeVisible).Select
Selection.AutoFill Destination:=Range(aRangeStr).SpecialCells(xlCellTypeVisible), _
Type:=xlFillSeries
Any hint how to fill the series only on filtered/visible cells?
任何提示如何仅在过滤/可见单元格上填充系列？
采纳答案by Siddharth Rout
Autofill doesn't work on the filtered range but there is alternative. :)
自动填充不适用于过滤范围，但有替代方法。:)
TRIED AND TESTED
久经考验
You can use this formula instead (I am assuming you are filtering on the basis of what is stored in Col A (See Sample Image)
您可以改用此公式（我假设您正在根据 Col A 中存储的内容进行过滤（请参阅示例图片）
I am adding + 1 as you are starting the series with value 2 in cell "c2"
当您在单元格“c2”中以值 2 开始系列时，我正在添加 + 1
=IF(A2="FILTER_TEXT",COUNTIF($A:A2,"FILTER_TEXT")+1,"")
And to adapt it to your example, use this code.
并使其适应您的示例，请使用此代码。
Sub Sample()
COL_SID_CURRENT = 3
COL_SID_CURRENT_STR = "c"
ROW_LAST = ActiveSheet.UsedRange.Rows.Count
' Start the series with value 2 in cell "c2"
Cells(2, COL_SID_CURRENT).Formula = "=IF(A2=""FILTER_TEXT"",COUNTIF($A:A2,""FILTER_TEXT"")+1,"""")"
aRangeStr = "" & COL_SID_CURRENT_STR & "2" ' -> "c2"
aRangeStr = aRangeStr & ":" & COL_SID_CURRENT_STR & ROW_LAST ' -> "c2:c24"
Range(aRangeStr).Formula = "=IF(A2=""FILTER_TEXT"",COUNTIF($A:A2,""FILTER_TEXT"")+1,"""")"
End Sub
Please change "FILTER_TEXT" to the relevant text. Also you might need to change the Reference Range. Like I mentioned, I am assuming that the filter is based on Values in Col A.
请将“FILTER_TEXT”更改为相关文本。此外，您可能需要更改参考范围。就像我提到的，我假设过滤器基于 Col A 中的值。
