填充空白单元格（变化） [英] Fill blank cells (Variation)

问题描述

我在A，B，C中有3个列标题。

根据我有可变量的行，但列A和B将始终具有数据。

列C可能有差距。我如何做类似的操作：
编辑>转到>特殊>空白，在公式栏中键入=，然后按向上箭头 Ctrl + 输入

除了宏只会上升到A的最后一行，没有进一步的。

我有：

  Sub FillCellsFromAbove（）
'关闭屏幕更新以提高性能
 Application.ScreenUpdating = False 
 On Error Resume Next 
'查看列A 
带列（3）
'对于空白单元格，将它们设置为等于
以上的单元格.SpecialCells（xlCellTypeBlanks ）.Formula == R [-1] C
'将公式转换为值
 .Value = .Value 
结束
 Err.Clear 
 Application.ScreenUpdating = True 
 End Sub 
  

然而，它从页面底部填充而不是最后一个A值的位置。

解决方案

不要使用所有列C - 首先确定列A中的数据扩展多远，然后在列C中获取许多单元格：

  Sub FillCellsFromAbove（）
 Dim R As Range，n As Long 
 
n = Range（A：A）。Rows.Count 
n = Cells（n ，A）。End（xlUp）.Row 
设置R =范围（单元格（1，3），单元格（n，3））
 
 Application.ScreenUpdating = False 
 On Error Resume Next 
使用R 
'对于空白单元格，将它们设置为等于
上的单元格.SpecialCells（xlCellTypeBlanks）.Formula == R [-1] C
'将公式转换为值
 .Value = .Value 
结束
 Err.Clear 
 Application.ScreenUpdating = True 
 End Sub 
  

I have an issue with filling blank cells of a column.

I have 3 Column headings in A, B, C.

Under that I have variable amounts of rows, but column A and B will always have data.

Column C could have gaps. How could I do something similar to: Edit > Go To > Special > Blanks, type = in the formula bars, hit the up arrow then Ctrl+Enter

EXCEPT, with the macro only going up until the last row of A and no further.

I have:

Sub FillCellsFromAbove()
    ' Turn off screen updating to improve performance
    Application.ScreenUpdating = False
    On Error Resume Next
    ' Look in column A
    With Columns(3)
        ' For blank cells, set them to equal the cell above
        .SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
        'Convert the formula to a value
        .Value = .Value
    End With
    Err.Clear
    Application.ScreenUpdating = True
End Sub

It however fills right from the bottom of the page and not from where the last "A" value is.

解决方案

Don't use all of Column C -- first determine how far the data in Column A extends and then grab that many cells in column C:

Sub FillCellsFromAbove()
    Dim R As Range, n As Long

    n = Range("A:A").Rows.Count
    n = Cells(n, "A").End(xlUp).Row
    Set R = Range(Cells(1, 3), Cells(n, 3))

    Application.ScreenUpdating = False
    On Error Resume Next
    With R
        ' For blank cells, set them to equal the cell above
        .SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
        'Convert the formula to a value
        .Value = .Value
    End With
    Err.Clear
    Application.ScreenUpdating = True
End Sub

这篇关于填充空白单元格（变化）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！