New to Editing VBA

[Andrew_UK]

Hi All,

I'm moderately experienced with recording simple macros in Excel. I've now been asked to build a far more complex Macro and I've got it working (mostly). I'm now at a point where I would like to tweak/improve the code for the purposes of making it easier to read - thing is I'm still very new to understanding the various tricks in VBA code writing.

So that it looks neater I'd like a particular set of formulae which are pre-set in cells G2 and H2 to autofill to the last Row in the spreadsheet (which will definitely be the last cell in columns A:B, and possibly the last cell in columns C:F as well). I tried recording this and it gives me this as part of the code:

Range("G2:H2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G2:H35414")

Fortunately this is the maximum range that could be required for the foreseeable future, but if the resulting report only covers 200 lines then I'd rather these formulae did also.

Rather than 'just' getting an answer I'd be really keen to learn how the formula you provide works. Thanks in advance for your help.

Best Regards,

Andrew_UK

 

[sericom]

lastrow = Columns("A:B").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("G2:H2").AutoFill Destination:=Range("G2:H" & lastrow)

lastrow is found by searching from the bottom of columns A & B for a cell with a value or formula in it, and assigns the row no.

 

[Andrew_UK]

That's brilliant, Thank you!!

I put it in now and it worked perfectly