How to fill in blank cells in this column, when the cells have to be populated with different values-CodePudding

I've been cracking my head on an issue I'm facing currently.

How can I make it (using VBA) so that column (B) gets populated with the first value (P), until it reaches the next value (R), then continues to get populated with it (R), until it reaches the next value, and so on until the values in column (C) end? (Like autofill)

The values in column (B) can be either (P) or (R), but not the same every time. As well as the amount of rows.

Should look like this (blank rows can be deleted)

I've tried a couple of things to make it work. First, I coded this:

Dim nb As Workbook

nb.Sheets(2).Select
Range("C2").Select
i = 2

Do Until IsEmpty(Range("C" & i))

   If InStr(1, Range("C" & i), "") Then
   nb.Sheets(2).Range("B" & i) = nb.Sheets(2).Range("B2")

End If

i = i   1

Loop

And as expected it only filled the first part of the column:

Like this:

I then tried to add more to the code:

nb.Sheets(2).Select
Range("C2").Select

    Selection.End(xlDown).Select
    Selection.End(xlDown).Select

i = ActiveCell

Dim inD As String

    ActiveCell.Offset(0, -1).Select
    
inD = ActiveCell

    ActiveCell.Offset(0, 1).Select

Do Until IsEmpty(Range("C" & i))

    If InStr(1, Range("C" & i), "") Then
    nb.Sheets(2).Range("B" & i) = inD

End If

i = i   1

Loop

But also as expected this doesn't work. It seems you can't assign cell position to i using ActiveCell. Now I'm getting the "runtime error 1004 : Method range of object - 'Global' failed"

I also tried the autofill using recording, but it didn't work either.

I would greatly appreciate help on this issue!

CodePudding user response：

You can follow this procedure:

Select the entire column B
Press Ctrl G for launching the "Goto" menu, choose "Special".
Choose "Blanks"
Click with your mouse in the formula bar and type =B2
Press Ctrl ENTER

In case you want this in a macro, just record it.

CodePudding user response：

Try that code. You will get similar output to Dominique's procedure but it will also remove blank rows.

Sub test()

Dim sht     As Worksheet: Set sht = ActiveSheet
Dim LastRow As Long
Dim myRng   As Range

'Find last row with data in sheet
LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

'Set Range to fill in with values
Set myRng = sht.Range("B2", "B" & LastRow - 1)
  
    'Loop through every cell in range and paste value from previous cell
    For Each cell In myRng
        If Not cell = "" Then
            If cell.Offset(1, 0) = "" And cell.Offset(1, 1) <> "" Then
                cell.Offset(1, 0).Value = cell.Value
            Else
                GoTo nextIteration
            End If
        
        End If
    
nextIteration:
    Next cell

    'delete empty rows
    For x = LastRow To 1 Step -1
        If WorksheetFunction.CountA(Rows(x)) = 0 Then
            sht.Rows(x).Delete
        End If
    Next

End Sub