在列中查找值,填满直到空白的单元格 [英] Find Value in Column, Fill Up 'till Non-Blank Cell
问题描述
我无法找到一种使用宏来执行以下操作的方法:
I am having trouble finding a way to use a macro to do the following:
我正在找到列A的最后一个填充行,并在其上偏移一列以将值粘贴到列B中.我想将放置在列B中的文本填充并复制到所有空白处上方的单元格,直到一个非空白的单元格.
I am finding the last populated row of Col A and offsetting one column over to paste a value in Column B. I would like to take that text that was put in Col B and fill up/copy-to all of the empty cells above it, until a non-blank cell.
每个循环都在B列中插入一个不同的字符串,这就是为什么我希望找到一种方法将该字符串粘贴到行上,直到遇到非空白单元格为止
Each loop inserts a different string in column B, which is why I'm hoping to find a way to paste that string up the row until it hits a non-blank cell,
例如:
A B
Header Header
9/30/14
9/30/14
9/30/14
9/30/14 text1
9/30/14
9/30/14
9/30/14
9/30/14 text2
外观如下:
A B
Header Header
9/30/14 text1
9/30/14 text1
9/30/14 text1
9/30/14 text1
9/30/14 text2
9/30/14 text2
9/30/14 text2
9/30/14 text2
很抱歉,这很令人困惑!任何帮助将不胜感激!
Apologies if this is terribly confusing! Any help would be greatly appreciated!
推荐答案
我真的很喜欢pnuts的解决方案,并建议如果您的数据如图所示那样简单,那么这部分VBA代码也应该能够提供所需的结果如果您正在寻找VBA解决方案:
I really like pnuts' solution and would recommend that if your data is as simple as shown, this bit of VBA code should also be able to provide the desired result if you're looking for a VBA solution:
Sub tgr()
Dim rngBlanks As Range
Dim BlankArea As Range
Set rngBlanks = Range("B1", Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlCellTypeBlanks)
For Each BlankArea In rngBlanks.Areas
BlankArea.Value = BlankArea.Cells(1).Offset(BlankArea.Cells.Count).Value
Next BlankArea
End Sub
这篇关于在列中查找值,填满直到空白的单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!