VBA to paste value above

Akbarov

Active Member
Joined
Jun 30, 2018
Messages
347
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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:
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top