View original (French)

Copy a cell every x rows

Solved
bix -  
Theo.R Posted messages 585 Status Member -
Hello, I have an Excel sheet with a column of 5200 grouped data (meaning the first is in cell A1 and the last in cell A5200). I would like to copy this data to another sheet, but I want one data point every 253 rows. How can I do this without copying each data point one by one? Is there, for example, a "paste every 253 rows" option?

Thank you in advance

SB

1 answer

  1.
    Theo.R Posted messages 585 Status Member 31
     
    Hello,

    In an adjacent column, you can calculate the rows for which you need to copy the value, then filter on that and copy all the relevant rows.

    The following formula to be entered in B1 and then dragged down will display "ok" for rows 1, 254, 507, 760, ... (every 253 rows starting from row 1):

    =IF(MOD(ROW(A1)-1,9)=0,"ok","")
    -1
    1. bix
       
      Hello Théo,

      Thank you for your response, but your formula will only allow me to see "ok" marked in each cell where I need to enter a value. However, I will then have to manually do the "copy-paste" every time "ok" is displayed. In my case, that's 5199 times (I’m not counting cell A1). It’s precisely this step that I’m trying to avoid.
      0
    2. Theo.R Posted messages 585 Status Member 31
       
      "then you filter on it and copy all the relevant lines."

      -> You just have a copy-paste to do to get all the lines.
      0
    3. bix
       
      Thank you very much, I understood the trick. The only thing is, I can't quickly stretch the line up to line 1310400. Any tips?
      0
    4. bix
       
      Oh, it's actually all good.
      Thank you very much for your help :)

      See you soon
      0
    5. Theo.R Posted messages 585 Status Member 31
       
      Alt+F11, in the top left select the sheet where your formulas to be extended are, then paste this code to extend the formula from E1 to E1310400: 
      Sub test()
       Range("E1").AutoFill Destination:=Range("E1:E1310400"), Type:=xlFillDefault
       Range("E1:E1310400").Value = Range("A1:A1000").Value 'pastes as values to avoid long recalculation
      End Sub

      Then, while keeping your click in the pasted text (vba code), you run the macro (green arrow in the action bar: "Run Sub F5"

      Wait for it to process all the lines and there you go :-), be careful! no CtrlZ is possible, so make it on a copy just in case ;)
      0