问题描述
为此:
我希望这组代码能够提取C列中的每个空白并在其中执行宏.如果我的工作表具有固定范围，这将很容易，但是，我的列表在行中不断增加...因此，我需要宏才能在空白单元格上运行宏并在那些填充的单元格上跳过.宏也应在列中最后一个填充的单元格上结束.
I would like the set of code to be able to pick up every blank in column C and perform a macro in it. It would have been easy if my sheet has a fixed range, however, my list is constantly increasing in rows... Hence, I would need the macro to be able to run macro on blank cells and skip on those filled cells. The macro should also end upon the last filled cell in the column.
Sub Testing()
Dim Rl As Long ' last row
Dim Tmp As Variant
Dim R As Long ' row counter
With ThisWorkbook.ActiveSheet ' modify to suit
Rl = .Cells(.Rows.Count, "C").End(xlUp).Row
' work on column C
For R = 1 To Rl ' start the loop in row 1
Tmp = .Cells(R, "C").Value
If Len(Tmp) Then
Cells(R, "C").Select
Call AutoFill
End If
Next R
End With
Sub AutoFill()
Application.EnableEvents = False
Dim rng As Range
Set rng = Range(Selection, Selection.End(xlDown))
Set rng = rng.Resize(rng.Rows.Count - 1, rng.Columns.Count)
rng.FillDown
End Sub
推荐答案
您的问题在这里:如果Len(Tmp)然后，那只是检查 Tmp 是否有长度.因此，这实际上是通过跳过空单元格来忽略它们.相反，您要选择其中包含值的单元格.
Your problem is here: If Len(Tmp) Then and that's just checking if Tmp has any length. So this actually ignores your empty cells by skipping them. Instead you are selecting cells with values in them.
请勿循环搜索某个范围内的所有单元格.而是只看那些感兴趣的空单元格.例如:
Do not loop all cells in a range. Instead just look at those empty cells of interest. For example:
Sub Testing()
Dim LR As Long, LC as Long
Dim rng As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
With ws
LR = .Cells(.Rows.Count, "A").End(xlUp).Row
LC = .Cells(LR, .Columns.Count).End(xlToLeft).Column
Set rng = .Range(.Cells(1, 1), .Cells(LR, LC))
If WorksheetFunction.CountBlank(rng) > 0 Then
For Each area In rng.SpecialCells(xlCellTypeBlanks).Areas
area.Offset(-1).Resize(area.Rows.Count + 1).FillDown
Next
End If
End With
End Sub
如您所见，我省略了.选择和 ActiveSheet ，因为这是糟糕的编码，通常根本不需要.
As you can see I left out .Select and ActiveSheet as that's poor coding and usually not needed at all.
这篇关于对于列中的每个空白单元格，运行一个自动填充宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！