[SOLVED] Defining the "last row" value (on a Sheet that grows each week) and using it in the Range object

Defining the "last row" value (on a Sheet that grows each week) and using it in the Range object

G'day all, thank you for your time

I am working on a tool that is designed to do alot of heavy formatting on a large sheet of raw data. The data is approximately 86 columns wide, which are fixed in place and do not grow. However, the rows are approximately 176,000 strong and grow by a few hundred each week.

Given my extremely low level of skill, I recorded the manual process of formatting as a macro and whittled it down to about 360 lines of code. I realise this can be optimised better, but I'm only really concerned about one issue with the Range() object.

My formatting does quite alot with the AutoFill tool, and in the recorded code it often would read like so:

Selection.AutoFill Destination:=Range("J2:J176413")

The issue being, as the raw data sheet grows each week, the final J row would be a larger number than 176413, and the macro would not AutoFill or otherwise format the newest rows.

To workaround this, I brute forced the first version of my tool by manually increasing this row value to 250,000 in each instance, which has bought me time. For example:

Selection.AutoFill Destination:=Range("J2:J250000")

It works, but it is rather inefficient and generates alot of blank rows that just slow the sheet down, particularly when trying to scroll up after a filter is applied.

What I am after is a way at the beginning of my Sub to define the final (newest) row of data as a variable, and then utilise that throughout the macro in the Range object, so that AutoFill is not going any further down than necessary.

A Pseudo-code illustration of my desire is something like this:

Dim LastRowValue = Range("A1").SpecialCells(xlLastCell).Row

code code code

Selection.AutoFill Destination:=Range("J2:J**LastRowValue**")

so that it knows what the final row to AutoFill to each time, without having to either manually update the macro each week and without having to brute force it with some arbitrarily large number.

My skillset is very limited so I am after the shortest/easiest to understand solution if possible - I've already idiot-proofed the macro from the end user (password protection, locks out their screen during use, alot of error fail-safes should they try anything goofy) so I don't require elegance, just simplicity

Please and thank you in advance!

Failed attempt below:

I've tried defining the last row as such, but cannot seem to get the syntax right for utilising it inside of the Range object

   Dim LastRowChecker As Long
   Dim LastColumnChecker As Long
   LastColumnChecker = Range("A1").SpecialCells(xlLastCell).Column


   Set LastRowChecker = Range("A1").SpecialCells(xlLastCell).Row
   Dim LastRowValue As Range
   Set LastRowValue = Cells(1, 1).Resize(LastRowChecker, LastColumnChecker)
   
   
   MsgBox LastRowValue

I can't seem to get the Range object to use LastRowChecker, and when I try to borrow the value for a Dim variable LastRowValue set as a Range, it generates an error, possibly about mis-matched variable types

Solution

Assuming that Column A can be used to determine the last row number. If not, please adjust the second argument in .Cells(.Rows.Count, 1) as necessary.

Microsoft documentation:

Range.End property (Excel)

Pls try.

With ActiveSheet ' modify as needed '
    Dim LastRowValue as Long
    LastRowValue = .Cells(.Rows.Count, 1).End(xlUp).Row

    ' code code code

    Selection.AutoFill Destination:=Range("J2:J" & LastRowValue)
End With