将excel单元格数据复制到列中最近的填充单元格 [英] Copy excel cell data till the nearest filled cell in column

问题描述

我是 Excel 的新手，我面临一些问题.

我有一个 Excel 表，我必须将它导入到我的另一个程序中.请考虑这种格式:

标题1 |小标题1 |小标题1||小标题2||小标题3||小标题4|小标题2 |小标题1||小标题2||小标题3||小标题4标题2 |小标题1 |小标题1等等..

问题在于，出于导入目的，我不能将任何单元格留空.所以，我想要一些论坛，通过它可以将 Heading1 复制到 Heading2 上方的所有单元格中，对于 column2 以及之后出现在所有行中的所有数据也是如此.p>

这会很有帮助，因为它会为我消除障碍.

解决方案

这可以非常快速地手动完成(无代码)或使用具有 xlBlanks 集合的 SpecialCells 以编程方式(简单代码)

1.手动

• 选择前两列中的单元格
• 按 F5 ... 特殊 ...空白
• 在公式栏中输入 =A1，其中 A1 是新选择的空白上方的第一个单元格
• 按住 Ctrl 键并按 Enter，一次在所有空白单元格中输入此公式

这写得很好这里包括如何将这些公式转换为值的说明

<强>2.VBA

这里提供了三个示例

第二个代码的修改版本适用于 A:B 列(这似乎是您的数据布局)

Sub FillColBlanks_Offset()'由里克·罗斯斯坦 2009-10-24'用上面的值填充列中的空白单元格'http://www.contextures.com/xlDataEntry02.html'由 brettdj 修改区域暗淡为范围，LastRow 为长出错时继续下一步有列(A:B")LastRow = .Find(什么:="*", SearchOrder:=xlRows, _搜索方向:=xlPrevious, _LookIn:=xlFormulas).Row对于 .Resize(LastRow) 中的每个区域._SpecialCells(xlCellTypeBlanks).AreasArea.Value = Area(1).Offset(-1).Value下一个结束于结束子

I am a total newbie to Excel, and there's a bit of problem I am facing.

I have an Excel sheet that I have to import into another program of mine. Please consider this format:

Heading1 |  Sub-heading1  |   Sub-Sub-heading1 
         |                |   Sub-Sub-heading2 
         |                |   Sub-Sub-heading3 
         |                |   Sub-Sub-heading4 
         |  Sub-heading2  |   Sub-Sub-heading1 
         |                |   Sub-Sub-heading2 
         |                |   Sub-Sub-heading3 
         |                |   Sub-Sub-heading4 
Heading2 |  Sub-heading1  |   Sub-Sub-heading1
and so on..

The problem is that for my import purpose I cannot leave any cells blanks. So, I wanted some forumla through which Heading1 can be copied to all the cells above Heading2, and similar for column2 and all the data appearing afterwards in all the rows.

This would be great help, since it would remove the roadblock for me.

解决方案

This can be done very quick either manually (no code) or programmatically (simple code) using SpecialCells which has a xlBlanks collection

1. Manually

• Select the cells in your first two columns
• Press F5 ... Special ...Blanks
• In the formula bar type =A1 where A1 is the first cell above your new selection of blanks
• Hold the Ctrl key and press Enter to enter this formula in all the blank cells at once

This is written up well here including instructions as to how to convert these formulae to values

2. VBA

Three samples are provided here

This modified version of the second code would work on columns A:B (which appears to be your data layout)

Sub FillColBlanks_Offset()
'by Rick Rothstein  2009-10-24
'fill blank cells in column with value above
'http://www.contextures.com/xlDataEntry02.html
'modified by brettdj
Dim Area As Range, LastRow As Long
On Error Resume Next
With Columns("A:B")
    LastRow = .Find(What:="*", SearchOrder:=xlRows, _
                    SearchDirection:=xlPrevious, _
                    LookIn:=xlFormulas).Row
    For Each Area In .Resize(LastRow). _
        SpecialCells(xlCellTypeBlanks).Areas
        Area.Value = Area(1).Offset(-1).Value
    Next
End With
End Sub

这篇关于将excel单元格数据复制到列中最近的填充单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！