Okay so I'm VERY new to using macros / VBA (I usually find work-arounds with built-in functions), and I have a question relating to a particular task on a repetitive report.
I'm combining and re-ordering a report that comes out of a Notes database in an Excel spreadsheet, which is basically formatted and is consistent, so I've used explicit references. The source data is coming from my worksheet labelled "TCR". This data has anticipated and actual start and finish dates, but I want to return the actual if it exists, and anticipated if not. I recorded the macro which copied the 4 columns of data into the "Report" worksheet, then entered and filled a simple IF() formula across and down. It works, but I've been lazy and made the bottom of the fill down range as row 108, but this could vary more or less.
The question is, how do I vary the fill down range to match the number of rows in my source data?
Here's the code:
' CopyTenderDates Macro
' Copies the Anticipated and Actual Tender Dates from the TCR into the Report sheet, then populates the IF() formula choice.
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(and(RC[-2]="""",RC[-4]=""""),"""",if(RC[-2]="""",RC[-4],RC[-2]))"
Selection.AutoFill Destination:=Range("L9:M9"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("L9:M108"), Type:=xlFillDefault
Selection.EntireColumn.Hidden = True