Copy the first value from a cell and Paste into all cells from one column with v

Question

Copy the first value from a cell and Paste into all cells from one column with vba

I need copy the value from cell A1 in column A and paste this value on the rest of the cell in this column A with reference to quantity of values in all cells from column B.

I need create a Macro

Example:

  Column A         Column B           

        1                    2

                              2

                              2

                              2

                              2

                              2

I need this results:

Column A         Column B           

        1                     2

        1                     2

        1                     2

        1                     2

        1                     2

        1                     2

Thanks !

Explanation / Answer

You indicated macro so provided.

Sub Fill_Blanks()
   'Dave Peterson construct

Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim Col As Long

    Set wks = ActiveSheet
    With wks
        Col = .Range("A1").Column

        Set rng = .UsedRange        'try to reset the lastcell
        LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
        Set rng = Nothing
        On Error Resume Next
        Set rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
                .Cells.SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0

        If rng Is Nothing Then
            MsgBox "No blanks found"
            Exit Sub
        Else
             rng.NumberFormat = "General"
            rng.FormulaR1C1 = "=R[-1]C"
        End If

    'replace formulas with values
        With .Cells(1, Col).EntireColumn
            .Value = .Value
        End With

    End With

End Sub

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.