CODEWITHSUNDEEP
excelvba
user11732489
user11732489

Reputation:

VBA: Filling blank cells with the value above it plus one

I have 5 sheets which have blank cells and I need to fill them up with the value above plus 1. For example: column A has 1,2, ,4,5 and I need a code that will make it 1,2,3,4,5

I have tried a code I found online but it just copies the value above it.

Sub FillBlanks()
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub

I need to fill all the blanks.

Upvotes: 1

Views: 6237

Answers (2)

AAA
AAA

Reputation: 3670

Your program would be simpler, faster, and more efficient by avoiding the use of Select as much as possible. Here is a correct program in one line:

Sub FillBlanks()
Columns("A:A").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C+1"
End Sub

To apply to all 5 sheets in your workbook:

Sub FillBlanks()
Dim ws as Worksheet, LastRow as Long
On Error Resume Next
For Each ws in ThisWorkbook.Worksheets
    LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    ws.Range("A1:A" & LastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C+1"
Next ws
End Sub

Upvotes: 0

teylyn
teylyn

Reputation: 35990

You can do that without VBA.

Select the range, hit F5 to open the Go To dialog, click Special and in the next dialog tick Blanks and click OK. That will select all blank cells in the selected range.

Now, without changing the selection, type a = sign, then hit the up arrow on the keyboard, then type +1. Hold down the Ctrl key and hit Enter.

enter image description here

If you still want to use VBA, change the last line of code to

Selection.FormulaR1C1 = "=R[-1]C+1"

That will give you the same result as the manual approach.

Upvotes: 2

Related Questions