在特定页面的多列中填充空白单元格 [英] Fill down blank cells in multiple columns in specific sheets

问题描述

我有以下列


< br>

 得分姓名
 5460姓名1姓氏1 
 2620 
 340 
 470 
 3445姓名2姓氏2 
 1290 
 1170 
 7460姓名3姓氏3 
 3520 
 360 
 1500 
 5048姓名4姓氏$ 4 $ b 830 
 490 
 1883 
 620 
 1208 
 185 
 426 
 653 
  / pre> 
 
 
 
 
我想填写列中的空单元格B& C ，直到在列A （在此示例中为第21行）的最后一行。 
 
 
 结果 
 
 
  
 
 
 分数姓氏
 5460姓名1姓氏1 
 2620姓名1姓氏1 
 340姓名1姓氏1 
 470姓名1姓氏1 
 3445姓名2姓氏2 
 1290姓名2姓氏2 
 1170姓名2姓氏2 
 7460姓名3 Lastname3 
 3520姓名3姓氏3 
 360姓名3姓氏3 
 1500名称3姓氏3 
 5048姓名4姓氏4 
 830姓名4姓氏4 
 490姓名4姓氏$ 4 
 1883姓名4姓氏4 
 620姓名4姓氏4 
 1208姓名4姓氏4 
 185姓名4姓氏4 
 426姓名4姓氏4 
 653姓名4姓氏$ 4 
  
 
 
目前我正在修改这个vba代码：
 
  Sub FillColBlanksSpecial（）
 
 Dim wks As Worksheet 
 Dim rng As Range 
 Dim rng2 As Range 
 Dim LastRow As Long 
 Dim col As Long 
 Dim lRows As Long 
 Dim lLimit As Long 
 
 Dim lCount As Long 
 On Error Resume Next 
 
 lRows = 2 
 lLimit = 1000 
 
设置wks = ActiveSheet 
使用wks 
 
 col = .Range（b1，c1）。列
 
设置rng = .UsedRange'尝试重置最后一个单元
 LastRow = .Cells.SpecialCells（xlCellTypeLastCell）.Row 
设置rng =没有
 
 lCount = .Columns（col）.SpecialCells（xlCellTypeBlanks）.Areas（1）.Cells.Count 
 
如果lCount = 0然后
 MsgBox在所选列中找不到空格
退出子
 ElseIf lCount = .Columns（col）.Cells.Count然后
当lRows< LastRow 
设置rng = .Range（.Cells（lRows，col），.Cells（lRows + lLimit，col））_ 
 .Cells.SpecialCells（xlCellTypeBlanks）
 rng.FormulaR1C1 = = R [-1] C
 lRows = lRows + lLimit 
循环
 Else 
设置rng = .Range（.Cells（2，col），.Cells（LastRow， col））_ 
 .Cells.SpecialCells（xlCellTypeBlanks）
 rng.FormulaR1C1 == R [-1] C
 End If 
 End With 
 
 End Sub 
  
 
 
 
 
 
  Question1 ：此代码仅在    上运行。如何运行在列B& C  
正如您在代码中看到的那样，我尝试了 col = .Range（b1，c1）。列，但无效。 
 
 
 
  Question2 ：如何在名单以 -A 和 -B 仅在相同的工作簿中？
 
 
 
在同一个工作簿中，我有hundrends的工作表，但我想仅运行 那些以上述后缀结尾。 
谢谢。
解决方案
您可以使用与循环中的活动工作表相同的变量循环通过工作表集合，检查工作表名称以查看是否需要处理。
  Sub FillColBlanksSpecial（）
 Dim wks作为工作表
 
对于每个wks在工作表
如果右（wks.Name，2）=-A或右（wks.Name，2）=-B然后
与wks 
带.Cells（1，1）.CurrentRegion 
带.Columns（B：C）
如果CBool​​（Application.CountBlank（.Cells））然后
 .SpecialCells（xlCellTypeBlanks）.FormulaR1C1 == r [-1] c
 End If 
 End With 
'un comment the next line if you want the formula to revert to values只有
'.Cells = .Cells.Value 
 End with 
结束
结束如果
下一个wks 
 
结束Sub 
  
我已经添加了一行，将重复的公式还原为其基础值，但留下评论。如果您只偏好值，请取消注释。
 
I have the following columns 



Score   Name    Last Name
5460    Name1   Lastname1
2620        
340     
470     
3445    Name2   Lastname2
1290        
1170        
7460    Name3   Lastname3
3520        
360     
1500        
5048    Name4   Lastname4
830     
490     
1883        
620     
1208        
185     
426     
653     


I want to fill down the empty cells in columns B & C until the last row with in Column A (which is row 21 in this example). 


Result




Score   Name    Last Name
5460    Name1   Lastname1
2620    Name1   Lastname1
340     Name1   Lastname1
470     Name1   Lastname1
3445    Name2   Lastname2
1290    Name2   Lastname2
1170    Name2   Lastname2
7460    Name3   Lastname3
3520    Name3   Lastname3
360     Name3   Lastname3
1500    Name3   Lastname3
5048    Name4   Lastname4
830     Name4   Lastname4
490     Name4   Lastname4
1883    Name4   Lastname4
620     Name4   Lastname4
1208    Name4   Lastname4
185     Name4   Lastname4
426     Name4   Lastname4
653     Name4   Lastname4


Currently I am trying to adapt this vba code:
Sub FillColBlanksSpecial()

Dim wks As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim LastRow As Long
Dim col As Long
Dim lRows As Long
Dim lLimit As Long

Dim lCount As Long
On Error Resume Next

lRows = 2
lLimit = 1000

Set wks = ActiveSheet
With wks

   col = .Range("b1,c1").Column

   Set rng = .UsedRange  'try to reset the lastcell
   LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
   Set rng = Nothing

    lCount = .Columns(col).SpecialCells(xlCellTypeBlanks).Areas(1).Cells.Count

    If lCount = 0 Then
        MsgBox "No blanks found in selected column"
        Exit Sub
    ElseIf lCount = .Columns(col).Cells.Count Then
        Do While lRows < LastRow
            Set rng = .Range(.Cells(lRows, col), .Cells(lRows + lLimit, col)) _
                           .Cells.SpecialCells(xlCellTypeBlanks)
            rng.FormulaR1C1 = "=R[-1]C"
            lRows = lRows + lLimit
        Loop
    Else
        Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
                       .Cells.SpecialCells(xlCellTypeBlanks)
        rng.FormulaR1C1 = "=R[-1]C"
    End If
End With

End Sub

 

Question1: this code only runs on Column B. How to run in in Column B & C
As you see in the code, I tried col = .Range("b1,c1").Column, but to no avail.

Question2: how to run this in sheets whose name end in -A and -B only in the same workbook?

In the same workbook, I have hundrends of sheets, but I want to run this only in those ending in the above suffixes.
Thanks.
解决方案
You can use the same variable that you were using for the active sheet in a loop to cycle through the worksheet collection, checking the worksheet name to see if it requires processing.
Sub FillColBlanksSpecial()
    Dim wks As Worksheet

    For Each wks In Worksheets
        If Right(wks.Name, 2) = "-A" Or Right(wks.Name, 2) = "-B" Then
            With wks
                With .Cells(1, 1).CurrentRegion
                    With .Columns("B:C")
                        If CBool(Application.CountBlank(.Cells)) Then
                            .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]c"
                        End If
                    End With
                    'un comment the next line if you want the formulas to revert to values only
                    '.Cells = .Cells.Value
                End With
            End With
        End If
    Next wks

End Sub
I've added a line to revert the duplicating formulas to their underlying values but left it commented. Uncomment the line if you prefer values only.

                        
这篇关于在特定页面的多列中填充空白单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！