Forum Discussion

RyanRR1983's avatar
RyanRR1983
Copper Contributor
Sep 24, 2020
Solved

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

Hello,

 

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! 

 

Best,

Ryan

Macros and VBA
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Sep 28, 2020

    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).

     

     

7 Replies

  • Charla74's avatar
    Charla74
    Iron Contributor
    Sep 25, 2020

    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(ISNUMBER($A7),$A7,"")

     

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

     

    =IF(ISNUMBER($A7),$A7*$C7,"")

     

    Hope this helps.

    • RyanRR1983's avatar
      RyanRR1983
      Copper Contributor
      Sep 26, 2020

      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!

      ~Ryan

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Sep 26, 2020

        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.

Resources