filling values up to last row in a dynamic range

Question

sorry guys I am conscious this is really simple but its been very frustrating trying to figure this out last hour. I would like to have the value "427" (Column D) go all the way down to the end of the last line of range. (Row 32) As it is a dynamic range, the range will have new entries over time. I used the following code to do it

  Sub EnterRemainingValues()
    Dim Workbook As Workbook
    Dim lastrow As Long
    Set Workbook = Workbooks("Workbook 1") 'name of workbook

    lastrow = Worksheets("Sheets1").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Range("D14:D" & lastrow).Value = "427"

The issue I believe i'm having is because the range starts from A13 rather than A1, so the values 427 only fill up the first 3 rows which corresponds to the height of the worksheet title (A1:A3). The code thinks thats the lastrow. What I would like is to fill column D with "427" all the way down to the last row starting from row 14. Thank you

EDIT: I forgot to mention - I am controlling "workbook1" which contains the range pictured from a different workbook ("workbookfinal")

Is the code in the workbook containing the worksheets? Or are they two different workbooks? — VBasic2008
@BigBen ive already tried that method does not seem to work. Please see my edit that i forgot to mention! apologies. I am controlling workbook1 from a different workbook — CleanRider
@VBasic2008 apologies, i forgot to mention, I have know corrected it. the code is in a different workbook("workbookfinal") and I am trying to run this code on the workbook pictured ("Workbook1") — CleanRider
Wouldn't use the J column solve the problem? lastrow = Worksheets("Sheets1").Cells(Rows.Count, 10).End(xlUp).Row — Evil Blue Monkey

VBasic2008 VBasic2008 · Accepted Answer · 2020-10-02T19:55:07

Try this:

Option Explicit

Sub EnterRemainingValues()
    
    ' Define workbook.
    Dim wb As Workbook ' You might need to use '.xlsm'
    Set wb = Workbooks("Workbook 1") ' name of workbook
    
    ' Define worksheet.
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1")
     
    ' Calculate last row.
    Dim lastrow As Long
    lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Write values.
    ws.Range("D14:D" & lastrow).Value = "427"

End Sub

filling values up to last row in a dynamic range

1 Answers