vba 用第一个空白上方的单元格中的值填充不连续的空白单元格

Fill non-contiguous blank cells with the value from the cell above the first blank

提问by jonestars

I want to know if there is a way to do this conditional in excel or open office:

我想知道是否有办法在 excel 或 open office 中有条件地执行此操作：

if the cell is empty then
    the cell will have the same value as the cell above it
else
    do nothing.

Should I use a macro for this? Please help me out; I have very little experience with Excel.

我应该为此使用宏吗？请帮帮我；我对Excel的经验很少。

c 1 | 10/21/2011
c 2 |
c 3 |
c 4 | 10/24/2011
c 5 |
c 6 |
c 7 | 10/25/2011
c 8 |
c 9 |
c10| 10/26/2011

1 | 10/21/2011
c 2 |
3 |
4 | 10/24/2011
c 5 |
c 6 |
7 | 10/25/2011
c 8 |
c 9 |
c10| 10/26/2011

回答by Nikhil

1. Select the range that contains blank cells you need to fill.

2. Click Home > Find & Select > Go To Special…, and a Go To Special dialog box will appear, then check Blanks option.

3. Click OK, and all of the blank cells have been selected. Then input the formula “=B2” into active cell B3 without changing the selection. This cell reference can be changed as you need.

4. Press Ctrl + Enter, Excel will copy the respective formula to all blank cells.

5. At this point, the filled contents are formulas, and we need to convert the formals to values. Then select the whole range, right-click to choose Copy, and then press Ctrl + Alt + V to active the Paste Special… dialog box. And select Values option from Paste, and select None option from Operation.

6. Then click OK. And all of the formulas have been converted to values.

1. 选择包含需要填充的空白单元格的范围。

2. 单击“开始”>“查找和选择”>“转到特殊”...，将出现“转到特殊”对话框，然后选中“空白”选项。

3. 单击“确定”，所有空白单元格都已被选中。然后在不更改选择的情况下将公式“=B2”输入到活动单元格 B3 中。可以根据需要更改此单元格引用。

4. 按 Ctrl + Enter，Excel 会将相应的公式复制到所有空白单元格。

5. 此时，填充的内容是公式，我们需要将形式转换为值。然后选择整个范围，右键单击选择复制，然后按 Ctrl + Alt + V 激活选择性粘贴...对话框。然后从粘贴中选择值选项，从操作中选择无选项。

6. 然后单击确定。并且所有公式都已转换为值。

回答by xsquires

In Excel:

If you have a table that someone has merged cells on or if you are copying a table from an html site where there can be many inconsistnacies in the copied data such as empty rows that really should have the same value as the previous row an easy way to fix this is go to an empty column and create the formula that follows.

This assumes that you have a header in A1 and A2 contains the first data and a valid row value.
If only every other cell is empty you can do the following:

= if(A2="",A1,A2)

The above function checks if A2 is blank, if it IS, it assigns the value of A1, if it is NOT, it assigns the value of A2.


However, if there are multiple empty rows that need to be filled it is better to do the following:
(assume cell whose value you want to duplicate in all subsequent empty cells begins at A2, you are willing to set the first two cells in the following column manually, column I:I is first empty column in your table)

在 Excel 中：

如果您有一个表格，有人在其上合并了单元格，或者您正在从 html 站点复制表格，其中复制的数据中可能存在许多不一致之处，例如空行实际上应该与前一行具有相同的值解决这个问题的一个简单方法是转到一个空列并创建下面的公式。

这假设您在 A1 中有一个标题，而 A2 包含第一个数据和一个有效的行值。
如果只有其他单元格为空，您可以执行以下操作：

= if(A2="",A1,A2)

上述函数检查 A2 是否为空，如果是，则分配 A1 的值，如果不是，则分配 A2 的值。


但是，如果有多个空行需要填充，最好执行以下操作：
（假设您要在所有后续空单元格中复制其值的单元格从 A2 开始，您愿意手动设置以下列中的前两个单元格，列 I:I 是表中的第一个空列）

Step One: In cells I2 and I3 ensure the proper vales are present for the associated rows.
Step Two: In cell I4 enter the following formula:

= if(A4="",if(A3<>"",A3,I3),A4)

The above function checks if A4 is blank, if it IS, it checks if A3 is NOT blank, if it indeed is NOT, it assigns the value of A3, if it too is blank, it assigns the value in I3, finally, if A4 was NOT blank, it assigns the value of A4.

You will have to drag the formula down manually (since there are empty rows).

第一步：在单元格 I2 和 I3 中，确保相关行存在正确的值。
第二步：在单元格I4中输入以下公式：

= if(A4="",if(A3<>"",A3,I3),A4)

上述函数检查A4是否为空，如果是，则检查A3是否为空，如果确实不是，则分配A3的值，如果也是空，它分配 I3 中的值，最后，如果 A4 不为空，则分配 A4 的值。

您必须手动向下拖动公式（因为有空行）。

Any empty row that follows data that you don't want should be easily identifiable by sorting the column by a key field.

通过按关键字段对列进行排序，您不需要的数据后面的任何空行都应该很容易识别。

As indicated by Robert above you are dealing with formula values so you can't just move them around, overwriting their source cells, but if you do a "special" paste you can paste the values directly over your source cells.

正如上面的 Robert 所指出的，您正在处理公式值，因此您不能只是移动它们，覆盖它们的源单元格，但是如果您进行“特殊”粘贴，您可以将值直接粘贴到源单元格上。

回答by Robert Ilbrink

This is what I once wrote:

这是我曾经写过的：

Sub FillInBlankCellsInColumns()
 'Go down and when you find an empty cell, put the value from the previous cell in there.
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim MyCounter As Long
MyCounter = 0
    For Each r In Selection
        On Error Resume Next
        If r.Value = "" Then
            r.Value = r.Offset(-1, 0).Value
        End If
    Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

回答by brettdj

Debra Dalgleish provides both the

Debra Dalgleish 提供了

• manual SpecialCells Method(that Excellll refers to)
• the VBA SpecialCells methodwhich seems to be more your area of focs

• 手动 SpecialCells 方法（即 Excelll 所指）
• VBA SpecialCells 方法似乎更适合您的关注领域

at her site under Excel Data Entry -- Fill Blank Cells in Excel Column

在她的网站下的Excel 数据输入 -- 在 Excel 列中填充空白单元格

回答by Robert Ilbrink

Alternatively, you could use the following formula in a new column:

或者，您可以在新列中使用以下公式：

D2: =IF(ISBLANK(C2),D1,C2)

D2： =IF(ISBLANK(C2),D1,C2)

• Create a new column next to C.
• Previous column D will now be named column E. Your data is in column C, the new data is temporarily created in column D.
• The first value from C1 must be copied manually to D1
• Then in D2 you put this formula. Copy the formula all the way down.
• Then copy column D and use Paste-Special Values Onlyto paste the results of column D over the existing data in column C.
• Now you can remove column D

• 在 C 旁边创建一个新列。
• 以前的 D 列现在将命名为 E 列。您的数据在 C 列中，新数据临时在 D 列中创建。
• C1 中的第一个值必须手动复制到 D1
• 然后在 D2 你把这个公式。一直向下复制公式。
• 然后复制 D 列并使用仅粘贴特殊值将 D 列的结果粘贴到 C 列中的现有数据上。
• 现在您可以删除列 D

This is a little more "work" but since you state that you are not experienced in Excel, writing a macro could be challenging.

这有点“工作”，但由于您声明您没有 Excel 经验，因此编写宏可能具有挑战性。

Regards,

问候，

Robert Ilbrink

罗伯特·伊尔布林克

回答by user2733648

use Vlookup

使用 Vlookup

=+VLOOKUP("Output",D1:G7,1)

• Replace D1 with the address of the starting cell to lookup
• Replace G7 with the address of the Ending cell to lookup
• Replace 1 with the column you want the value to be returned from

• 将 D1 替换为要查找的起始单元格的地址
• 将 G7 替换为要查找的 Ending 单元格的地址
• 将 1 替换为您希望从中返回值的列

    Apple   1       Apple
            2       Apple
            7       Apple
    Mango   3       Mango
    =====================
    col1    Col2    Formula