Use a macro to autofill a column until the end of the data in another row

Occasional Contributor


I'm consolidating category data ahead of the holidays and it's taking a lot of time to do it manually. Basically, I need some help with the following problem:


The Macro should select the first cell with data (A1 in the attached spreadsheet) and autofill it into column E. until the last row before the space (E40 in this case). Then it will select the second category (A42 in the attached) and autofill into column E for rows E46-E59 and so on and so forth.


The problem with this is, that the number of rows with data always change every week. So I cannot use a static row number although the number of categories will likely stay the same. 


Any help would be great. Thank you so much! 




7 Replies

Hi @RyanRR1983 


If I understand correctly, you want to duplicate values from column A to column E, right?  If so, you can use the following formula from cell E7 down:




If you wanted a calculation rather than duplication, the following would be an example of that:




Hope this helps.


@Charla74 Thank you for the reply. 


I'm trying to create a macro that will automate adding the category data in cell A1, A42, A60 and so on to each of the products listed. I've included an example of what the spreadsheet should look like complete. Any thoughts on how to approach this problem?


Thank you!



@RyanRR1983 I suspect that your next step in this process is to perform some sort of analysis on Cost, Qty and Price per Product Number across different Categories. If so, there is no need to keep the category headers and empty rows between the groups of product numbers.


If my suspicion is correct, a relatively simple Get&Transform Data (a.k.a.PowerQuery or PQ) operation will fill down the Category to each relevant product record and create one long list that can be analysed, grouped, sorted, filtered however you want it, e.g. in a Pivot Table. Sheet2 in the attached workbook contains the end result of the PQ. I dare to say that PQ is easier to learn and maintain than VBA, in case you are new to both.


@Riny_van_EekelenThank you for the help! You are correct - I'm getting the data and cleaning it up so it's ready for analysis. 


To be honest, I'm not familiar with the Get&Transform Data (a.k.a.PowerQuery or PQ) operation. How did you use it to create Sheet 2 in the attached? Any help you can provide would be great. Thank you so much!




Best Response confirmed by RyanRR1983 (Occasional Contributor)

@RyanRR1983 The attached link takes you to, what I considered, a great help in learning PQ. As suggested in this blog, PQ has a bit of a learning curve, but once you get over that it will add a tremendous "toolbox" to Excel.


If you follow the first steps, (especially "Importing data from tables") you'll learn how to navigate to the query in the file I uploaded and look at the applied steps. In summary, the query loads the original data, creates a column with the category headers only and gets rid of empty rows. Then, it loads the transformed table back to Excel (that's what you find in Sheet2).




@Riny_van_Eekelen Thank you again! For some reason I can't find the link you mentioned. Would you mind resending it? 


@RyanRR1983 Sorry about that. Something must have gone wrong earlier.