VBA to paste value above
Results 1 to 4 of 4

Thread: VBA to paste value above

  1. #1
    Board Regular
    Join Date
    Jun 2018
    Posts
    85
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to paste value above

    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?

    Code:
    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
    Last edited by Akbarov; Aug 25th, 2019 at 04:45 AM.

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,302
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to paste value above

    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
    Last edited by James006; Aug 25th, 2019 at 04:55 AM.

  3. #3
    Board Regular
    Join Date
    Jun 2018
    Posts
    85
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to paste value above

    Thanks thats excatly what i needed

  4. #4
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,302
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to paste value above

    You are welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •