View original (French)

Excel: delete one row out of ten

Aurélie -  
 Aurélie -
We are looking for a solution to keep only one row out of every ten in a large table. The table has 3 columns: first column with numbers from 1 to ..., second column numeric data, third column time. We want to sort by the first column and retain only the multiples of 10: 1, 10, 20, 30, etc. If someone can help me ;-) We use Excel 2007. Aurélie. Configuration: Windows XP / Firefox 3.6.6

2 answers

  1.
    michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello,

    isn’t there a “edit -> delete 9 lines out of 10” feature? lol
    I think doing everything by hand is the only solution...
    advice to Excel pros...

    in other words: I master Excel perfectly and if I don’t know how to do it, does that mean you have to be a pro?...

    Aurélie,
    attached is a mockup with about 300 lines
    the VBA code is designed for a large number of lines

    http://www.cijoint.fr/cjlink.php?file=cj201009/cijxEG8CN4.xls

    currently the result is on Sheet2 to keep a record of the original. if you want to delete the original that won’t pose a problem for adjusting the code

    tell me if that works for you

    best regards,
    Michel

    :-x
    0
  2. Aurélie
     
    It looks like it’s working, but tell me, where can I find the code you used?

    I’m far from being a pro :-s

    ;-) Thanks!
    0
    1. Aurélie
       
      Ok I found the macro, it’s great!!!

      Thanks, that’s going to help us a lot.

      If we want to do all 50 for example, where should we change it?

      Thanks again!
      0
    2. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      for the 50 or 100 or 20 I’ll look and tell you...
      0
    3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      and here is the macro; the modifications are in bold 

      Const premlig As Byte = 2 ' 1° ligne du tableau Const saut As Integer = 50 ' pas des lignes à conserver Sub garder_1sur10() Dim nbre_lig As Long, derlig As Long, lig As Long, ind_t As Long Dim tab_10 ReDim tab_10(2, 0) 'tableau recueuillant les valeurs des 3 colonnes 'dernier ligne du tableau-array multiple de 10 With Sheets(1) nbre_lig = Int(.Range("A" & .Cells.Rows.Count).End(xlUp).Row / saut) derlig = (nbre_lig * saut) + premlig - 1 '----- ----------------------------remplit le tableau-array 'départ 1 n'est pas multiple de "saut" tab_10(0, 0) = .Cells(premlig, 1) tab_10(1, 0) = .Cells(premlig, 2) tab_10(2, 0) = .Cells(premlig, 3) ReDim Preserve tab_10(2, 1) 'ligne dont colonne A multi^ple de "saut" ind_t = 1 'indice tableau For lig = premlig + saut - 1 To derlig Step saut tab_10(0, ind_t) = .Cells(lig, 1) tab_10(1, ind_t) = .Cells(lig, 2) tab_10(2, ind_t) = .Cells(lig, 3) ind_t = ind_t + 1 ReDim Preserve tab_10(2, ind_t) Next ReDim Preserve tab_10(2, ind_t - 1) End With 'restitue en feuille 2 Application.ScreenUpdating = False With Sheets(2) .Range("A2:C" & .Cells.Rows.Count).ClearContents .Range("A2").Resize(nbre_lig + 1, 3) = Application.Transpose(tab_10) .Range("C2").Resize(nbre_lig + 1, 1).NumberFormat = "h:mm:ss;@" .Activate End With End Sub


      j'ai essayé avec 50, mais ca doit marcher aussi avec 21 ou 37....
      0
    4. Aurélie
       
      Thank you so much :-)
      0