Auto ranging my automate script in Excel

Hello,

I am trying to automate a monthly process where I am receiving a set of data. The column width is the same, but rows can vary drastically. I automated a formula in the last column, and I am duplicating it for every row, but the formula is being pulled to the end of the sheet, instead of to the last row that has data. How can I stop the code from going past the last row containing data? And I am trying to accomplish this using the automate tab in Excel.

Thank you

Maybe getlastrow

https://learn.microsoft.com/en-us/ja...trow-member(1)

For whatever reason, its not letting me upload the file. I followed the instructions, and selected the file, but it just does not appear anywhere. Perhaps I am being blocked from attaching or uploading?

But here is a screen grab. My issue essentially lies in the highlighted line. I need that to stop at the last row that contains data. I ve been told this is possible, just not how exactly.

Attachment 863532

function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Set fill color to #B4C6E7 for range I1 on selectedSheet
selectedSheet.getRange("I1").getFormat().getFill().setColor("#B4C6E7");
// Set range I1:I2 on selectedSheet
selectedSheet.getRange("I1:I2").setFormulasLocal([["Movement to Book"],["=G2-H2"]]);
// Paste to extended range obtained by extending down from range I2 on selectedSheet from range I2 on selectedSheet
selectedSheet.getRange("I2").getExtendedRange(ExcelScript.KeyboardDirection.down).copyFrom(selectedSheet.getRange("I2"), ExcelScript.RangeCopyType.formulas, false, false);
// Add a new worksheet
let sheet3 = workbook.addWorksheet();
// Add a new pivot table on sheet3
let newPivotTable = workbook.addPivotTable("PivotTable4", selectedSheet.getRange("A1:I1048576"), sheet3.getRange("A3"));
// This action currently can't be recorded.
// Add pivot field to a hierarchy in newPivotTable
newPivotTable.addRowHierarchy(newPivotTable.getHierarchy("Co Code"));
// Add pivot field to a hierarchy in newPivotTable
newPivotTable.addRowHierarchy(newPivotTable.getHierarchy("PRFT_CENTER_ID"));
// Add pivot field to a hierarchy in newPivotTable
newPivotTable.addDataHierarchy(newPivotTable.getHierarchy("Movement to Book"));

Thank you
}

Sub AutomaticRanking()
Dim ws As Worksheet
Dim lastRow As Long

' Specify the sheet on which you want to perform automation
Set ws = ThisWorkbook.Sheets("Sheet_name")

' Determine the last row with data in column A (you can select any other column)
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' Your automation code you want to execute

' Example:
For i = 2 To lastRow ' Assume that the first row contains headers
'Your code is here
Next i
End Su
This code identifies the last row of data in column A and runs your automation only up to that row. Replace ""SheetName" with the name of the sheet you want to automate on, and change the row and column range accordingly if needed.

Originally Posted by ByteMarks

Does this work for you?

PHP Code: 

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Set fill color to #B4C6E7 for range I1 on selectedSheet
    selectedSheet.getRange("I1").getFormat().getFill().setColor("#B4C6E7");
    // Set range I1:I2 on selectedSheet
    selectedSheet.getRange("I1:I2").setFormulasLocal([["Movement to Book"], ["=G2-H2"]]);

    let lr = selectedSheet.getRange("G1").getSurroundingRegion().getLastCell();

    let TheRange= "I3:"+lr.getAddress();
    // Paste to extended range obtained by extending down from range I2 on selectedSheet from range I2 on selectedSheet

    selectedSheet.getRange(TheRange).copyFrom(selectedSheet.getRange("I2"), ExcelScript.RangeCopyType.formulas, false, false);


// etc etc
} 


Um Dude! Thank you!!! Worked like a charm, i just copied and pasted. So if I want to apply the same principal to another worksheet, I just have to modify the code there as well? I mean in theory, it should work for any worksheet within the same parameters?

Great. It references the active worksheet so it should indeed work for any worksheet within the same parameters.

Hey ByteMarks. I tried to use this code in a following worksheet, but realized its slightly different. Hoping you can advise. The code below is first modifying the first row with some formulas, but then once again pulling the formula down (to the 4000th line, instead of to the end), but I also want it to stop when there is no more data to pull. Except this is pulling from a neighboring worksheet, so thus it needs to detect that the line is blank on the other worksheet. I am putting current coding below:

function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Set range A4 on selectedSheet
selectedSheet.getRange("A4").setFormulaLocal("=IF('Insights Pivot'!C5<0,40,50)");
// Set range D4:E4 on selectedSheet
selectedSheet.getRange("D4:E4").setFormulasLocal([["=ABS('Insights Pivot'!C5)","='Insights Pivot'!B5"]]);
// Set range H4 on selectedSheet
selectedSheet.getRange("H4").setFormulaLocal("='Insights Pivot'!A5");
// Set range P4 on selectedSheet
selectedSheet.getRange("P4").setFormulaLocal("=IF('Insights Pivot'!C5<0,50,40)");
// Paste to range A4:R4000 on selectedSheet from range A4:R4 on selectedSheet
selectedSheet.getRange("A4:R4000").copyFrom(selectedSheet.getRange("A4:R4"), ExcelScript.RangeCopyType.all, false, false);
}

Thanks

Bytemarks your spot on man. Code worked, almost perfectly. Its just using the last blank row, instead of stopping before that. But its so close to perfect, im not complaining!!! Thank you once again.

Great.
You could try not adding the 1 at the end of this line and see if that fixes it.

PHP Code: 

 //Get the lastrow from the insights sheet
    let lr = wsInsights.getRange("C5").getSurroundingRegion().getLastCell().getRowIndex() + 1; 


Originally Posted by ByteMarks

Great.
You could try not adding the 1 at the end of this line and see if that fixes it.

PHP Code: 

 //Get the lastrow from the insights sheet
    let lr = wsInsights.getRange("C5").getSurroundingRegion().getLastCell().getRowIndex() + 1; 


Worked like a charm! Thank you once again. Is this where i write SOLVED?

Yep did both. Thank you

Glad I could help.