excel - 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")

标签： excelvba

Answer 1

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