CODEWITHSUNDEEP
vbaexcel
Mehdi Flame
Mehdi Flame

Reputation: 63

Fill ID number in a certain order in excel VBA

I have column A that contains id numbers that is structured like this :

     A
R1   1
R2   
R3   2
R4
R5
R6   3

I am trying to fill blank cells in column A with the value of previous populated cell , the problem is that i don't have a constant number of blank cells each time a have a different number in this example the first time i have one blank cell after A1 in the second time i have 3 blank cells after A3 the output that i want is like this :

  A
R1   1
R2   1
R3   2
R4   2
R5   2
R6   3

It seems that with my fairly basic knowledge of VBA i can't think of anyway to achieve that output can you please help me out?

Upvotes: 0

Views: 164

Answers (1)

YasserKhalil
YasserKhalil

Reputation: 9538

Try this code

Sub Test()
Dim rng         As Range
Dim i           As Long

Set rng = Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row)

For i = 2 To rng.Rows.Count
    If rng.Cells(i, 1).Value = "" Then rng.Cells(i, 1).Value = rng.Cells(i - 1, 1).Value
Next i
End Sub

Another code

Sub Fill_Blanks()
With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub

Upvotes: 1

Related Questions