vba 用上面的值填充任何空单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20436835/
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
Filling any empty cells with the value above
提问by Aleksei Nikolaevich
I want to fill in all empty cells using values of above cells
我想使用上述单元格的值填充所有空单元格
state name
IL Mike
Sam
CA Kate
Bill
Leah
Should be as follows
应该如下
state name
IL Mike
IL Sam
CA Kate
CA Bill
CA Leah
I tried the following
我尝试了以下
Sub split()
Dim columnValues As Range, i As Long
Set columnValues = Selection.Area
Set i = 1
For i = 1 To columnValues.Rows.Count
If (columnValues(i) = "") Then
columnValues(i) = columnValues(i - 1)
End If
Next
End Sub
I get an error when I set i. How can I modify my code
设置时出现错误i。如何修改我的代码
采纳答案by Netloh
It is because ishould be defined as i=1. There are although a few other problems with the code. I would change it to something like this:
这是因为i应定义为i=1。尽管代码还有一些其他问题。我会把它改成这样的：
Sub split()
Dim columnValues As Range, i As Long
Set columnValues = Selection
For i = 1 To columnValues.Rows.Count
If columnValues.Cells(i, 1).Value = "" Then
columnValues.Cells(i, 1).Value = columnValues.Cells(i - 1, 1).Value
End If
Next
End Sub
回答by pnuts
For those not requiring VBA for this, select ColumnA, Go To Special..., Blanks and:
对于那些不需要 VBA 的人，请选择 ColumnA、转到特殊...、空白和：
Equals (=), Up (▲), Ctrl+Enter
等于 ( =), 向上 ( ▲), Ctrl+Enter
should give the same result.
应该给出相同的结果。
回答by brettdj
Given you asked for VBA, there is a quicker way than looping (the VBA equivalent of what pnuts posed above, with the additional step of removing the formula at the end):
鉴于您要求使用 VBA，有一种比循环更快的方法（VBA 相当于上面提出的 pnuts，并在最后附加了删除公式的步骤）：
On Error Resume Next
With Selection.SpecialCells(xlCellTypeBlanks)
.FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
回答by lexabu
Here is the whole module, I pasted the formulas as values at the end.
这是整个模块，我在最后粘贴了公式作为值。
Sub FillBlanksValueAbove()
Dim sName As String
sName = ActiveSheet.Name
Dim ws As Worksheet
Dim lastRow As Long, lastCol As Long
Dim rng As Range
'Set variable ws Active Sheet name
Set ws = Sheets(sName)
With ws
'Get the last row and last column
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
'Set the range
Set rng = .Range(.Cells(1, 1), .Cells(lastRow, lastCol))
rng.Select
'Select Blanks
rng.SpecialCells(xlCellTypeBlanks).Select
'Fill Blanks with value above
Selection.FormulaR1C1 = "=R[-1]C"
'Paste Formulas as Values
rng.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End With
End Sub
回答by Vitaliy Ozerov
Sub fill_blanks()
Dim i As Long
i = 2 ' i<>1 because your first raw has headings "state " "name"
'Assume state is in your cell A and name is in your cell B
Do Until Range("B" & i) = ""
Range("B" & i).Select
If ActiveCell.FormulaR1C1 <> "" Then
Range("A" & i).Select
If ActiveCell.FormulaR1C1 = "" Then
Range("A" & i - 1).Copy
Range("A" & i).PasteSpecial Paste:=xlPasteValues
Else
i = i + 1
End If
Else
i = i + 1
End If
Loop
End Sub
结束子
