Dynamic Autofill in Macro

[yagu99]

I'm wondering if there is a way to perform a dynamic autofill & stop the autofill when it comes to a blank cell. I need the autofill to use the exact data within the other cells of the range.

I currently have the autofill where I specify the range (in this case it would be F1 - F26):

Range("F1").AutoFill Destination:=Range("F1:F26"), Type:=xlFillDefault


I have multiple spreadsheets that I need to perform an autofill but, they are not the same range.

For example,
One Spreadsheet may have a range of F1 - F26, another spreadsheet's range could be F1 - F8, another spreadsheet's range could be F1-F18, etc.. . They all have an empty cell at the end of the range so, I was thinking I could use this in helping me specify the range of each spreadsheet.

Thanks,
yagu99

 

[Peter_SSs]

Bruno

Welcome to the MrExcel board!

I'm assuming that the date in L3 and the True/False in column J is only required to get rid of the old data and does not need to be kept after the code has run. If this is incorrect then please advise, and also desribe what columns are used for your main data. Is it from column A to column I?

In any case, test this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Report_modification()<br>    <br>    <SPAN style="color:#007F00">'Turn of screenupdating for speed & stop screen flicker</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <br>    <SPAN style="color:#007F00">'Insert today's date in L3</SPAN><br>    Range("L3").Value = <SPAN style="color:#00007F">Date</SPAN><br>    <br>    <SPAN style="color:#007F00">'Inserts formula (no need for IF) & fills down to end of data</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("J1:J" & Range("I" & Rows.Count).End(xlUp).Row)<br>        .FormulaR1C1 = "=RC[-1]<=R3C12"<br>        <br>        <SPAN style="color:#007F00">'Replace the formulas with their values because L3 is about to be cleared</SPAN><br>        .Value = .Value<br>        <br>        <SPAN style="color:#007F00">'Clear L3</SPAN><br>        Range("L3").ClearContents<br>        <br>        <SPAN style="color:#007F00">'Filter for True to delete old stuff</SPAN><br>        .AutoFilter Field:=1, Criteria1:=<SPAN style="color:#00007F">True</SPAN><br>        <br>        <SPAN style="color:#007F00">'Delete visible rows, except row 1</SPAN><br>        .Offset(1).EntireRow.Delete<br>        <br>        <SPAN style="color:#007F00">'Remove the autofilter</SPAN><br>        .AutoFilter<br>        <br>        <SPAN style="color:#007F00">'Clear column J</SPAN><br>        .ClearContents<br>        <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#007F00">'Turn screenupdating back on</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>