none
VBScript to automatically drag down formla to last row based on column "A" RRS feed

  • Question

  • Hi Folks -

    I'm struggling with a solution to be able to drag down a formula from "N3" down to the last row of data for Column A.

    Here is what I have thus far:

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    
    Set objWorkbook1= objExcel.Workbooks.Open("C:\Users\chtakac\Documents\Z\ZZ\Employees Accessxls")
    Set objWorksheet = objWorkbook1.Worksheets("Sheet1")
    	
    Set sourceRange = objWorksheet.Range("A")
    Set fillRange = objWorksheet.Range("N3:N30")
    sourceRange.AutoFill fillRange
    
    
    objWorkbook1.Save
    objWorkbook1.Close

    As you can see, I can set a hardcoded range, but I dont want to do that. I need it dynamic.
    Can anyone please assist?

    Friday, May 26, 2017 8:16 AM

All replies

  •   Set objExcel = CreateObject("Excel.Application")
      objExcel.Visible = True
      
      Set objWorkbook1 = objExcel.Workbooks.Open("C:\Users\chtakac\Documents\Z\ZZ\Employees Accessxls")
      Set objWorksheet = objWorkbook1.Worksheets("Sheet1")
    
      'Define constant from Excel
      xlUp = -4162
      'Get the last used cell in column A
      Set LastCell = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp)
      'The cell(s) to fill down
      Set SourceRange = objWorksheet.Range("N3")
      'Over this range
      Set FillRange = objWorksheet.Range("N3:N" & LastCell.Row)
      'Do it
      SourceRange.AutoFill FillRange
    
      objWorkbook1.Save
      objWorkbook1.Close
    

    Friday, May 26, 2017 9:54 AM
  • This works great!!! Thank you so much have a great weekend!
    Saturday, May 27, 2017 1:02 AM
  • I tried the above and got a compile error "assignment to constant not permitted on the xlUp = 

    Tuesday, March 26, 2019 3:45 PM