VBA to paste value above

[Akbarov]

Hey community,

I use following formula, to fill blank cells with value above. I wanna know how can i start this code from 5th row and end in 1000 row? in second column?

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

Thanks

 

[James006]

Hello,

The description of what you need is not extremely clear ...

Regarding the row # 5

Set rng = .Range(.Cells(5, 1), .Cells(lastRow, lastCol))

Hope this will help

 

[Akbarov]

Thanks thats excatly what i needed

 

[James006]

You are welcome