vba 循环遍历非空白单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45180061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Loop Through Non Blank Cells
提问by Jade Bowen
I just want to know how to loop through the non blank cells on Column A. What I'm trying to do is copy the contents on [A1:B1] to be added on top of each non blank cells on Column A. So far I have counted the non blank cells on column A but I'm stuck. I know that an Offset function should be used for this.
我只想知道如何遍历 A 列上的非空白单元格。我要做的是复制 [A1:B1] 上的内容，以添加到 A 列上的每个非空白单元格之上。到目前为止我已经计算了 A 列上的非空白单元格，但我被卡住了。我知道应该为此使用 Offset 函数。
Here's my code so far:
到目前为止，这是我的代码：
Dim NonBlank as Long
NonBlank = WorksheetFunction.CountA(Worksheet(1).[A:A])
For i = 1 to NonBlank
[A1:B1].Copy Offset(1,0). "I'm stuck here"
Next i
采纳答案by PatricK
As example to simulate the effect of Ctrl-Downfrom Cell A1 and display the Address, Value in the Immediate Window:
作为模拟Ctrl-Down来自单元格 A1的效果并在立即窗口中显示地址、值的示例：
Sub HopToNextNonBlankCellBelow()
Dim oRng As Range
Set oRng = Range("A1")
Debug.Print "Cell Address", "Cell Value"
Do
Set oRng = oRng.End(xlDown)
If Not IsEmpty(oRng) Then Debug.Print oRng.Address(0, 0), oRng.Value
Loop Until oRng.Row = Rows.Count
Set oRng = Nothing
End Sub
回答by Subodh Tiwari sktneer
If you are trying to fill the headers for each Product, try this...
如果您要为每个产品填充标题，请尝试以下操作...
Sub FillHeaders()
Dim lr As Long
Dim Rng As Range
lr = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
On Error Resume Next
Range("A1:B1").Copy
For Each Rng In Range("A3:A" & lr).SpecialCells(xlCellTypeConstants, 2).Areas
If Rng.Cells(1).Value <> Range("A1").Value Then
Rng.Cells(1).Offset(-1, 0).PasteSpecial xlPasteAll
End If
Next Rng
Application.CutCopyMode = 0
Application.ScreenUpdating = True
End Sub
回答by PGSystemTester
Try this... I've (probably) overcounted the rows at 1000, but it likely won't make a difference with your performance. If you wanted to be more precise, there are hundreds of articles on how to find the last row of a range. As for the Offsetfunction, it references a cell in relation to the one we're looping through. In the example below, the code is saying cell.offset(0,1)which means one cell to the right of the cell we are currently looping through. A clearer (less loopy!) example would be if you typed: Range("A10").offset(0,1)it would be the same as typing Range("B10")
试试这个...我（可能）多计了 1000 行，但它可能不会对你的表现产生影响。如果你想更精确，有数百篇关于如何找到一个范围的最后一行的文章。至于Offset函数，它引用一个与我们正在循环的单元格相关的单元格。在下面的示例中，代码表示cell.offset(0,1)这意味着我们当前循环的单元格右侧的一个单元格。一个更清晰（不那么乱）的例子是，如果你输入：Range("A10").offset(0,1)它与输入相同Range("B10")
Dim Cell As Range
For Each Cell In Range("A2:A1000").Cells
If Not IsEmpty(Cell) Then
Cell.Offset(0, 1).Value = Cell.Value
End If
Next Cell
