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

问题描述

我是Excel的一个新手，有一个我面对的问题。

我有一个Excel表，我必须导入到另一个程序矿。请考虑这种格式：

  Heading1 |子标题1 |子子标题1 
 | |子子标题2 
 | |子标题3 
 | |子标题4 
 |子标题2 |子子标题1 
 | |子子标题2 
 | |子标题3 
 | |子标题4 
 Heading2 |子标题1 |子标题1 
等等.. 
  

问题是，目的我不能留下任何细胞空白。所以，我想要一些forumla， Heading1 可以复制到 Heading2 上面的所有单元格，类似于column2和

这将是很大的帮助，因为它会为我移除包版。

SpecialCells 手动（无代码）或通过编程方式（简单代码）快速完成此操作， a xlBlanks集合

1。

• 选择前两列中的单元格


• 按F5 .. 。Special ... Blanks


• 在公式栏中键入= A1其中A1是新选择的空格上方的第一个单元格


• 按住Ctrl键，然后按Enter键即可在所有空白单元格中同时输入此公式

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

2。 VBA

提供三个样本：这里

此修改版本的第二个代码适用于A栏：B（看起来是您的数据布局）

  Sub FillColBlanks_Offset（）
'由Rick Rothstein 2009-10-24 
'填写栏中的空白单元格的值高于
 'http://www.contextures.com/xlDataEntry02.html 
'由brettdj修改
 Dim Area As Range，LastRow As Long 
 On Error Resume Next 
使用列（ A：B）
 LastRow = .Find（what：=*，SearchOrder：= xlRows，_ 
 SearchDirection：= xlPrevious，_ 
 LookIn：= xlFormulas）.Row 
每个区域.Resize（LastRow）。 _ 
 SpecialCells（xlCellTypeBlanks）.Areas 
 Area.Value = Area（1）.Offset（-1）.Value 
 Next 
 End With 
 End Sub 
  

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屋！