Create and delete rows if not 5

[susanaidds]

Hello,

I need to create or delete rows if the space between two cells isn't the same.
I can show an exemple.

I need to have 5 rows between each CDS cell, so I need to delete the first row if there are 6, or create 2 rows if there is only 3.
How can I do that with a formula or a macro?

Thank you in advance

 

[Rick Rothstein]

If there are too many rows between the CDS, which row (or rows) should be deleted... the first one(s) under the CDS or the last one(s) just before the next CDS?

Also, when inserting new rows to make the count 5, do we insert them at the beginning or end of the existing data rows? Also, do we leave the inserted rows blank or fill them with something (if so, what)?

 

[susanaidds]

If there are too many rows it should delete the firts one or two under CDS. And when there are the need to insert rows it's preferable to insert then in position 1 with the text "gene" and position 3 with the text "UniProtID". But if it's not possible just under CDS (position 1 and 2).

Thank you Rick

 

[Rick Rothstein]

If there are too many rows it should delete the firts one or two under CDS. And when there are the need to insert rows it's preferable to insert then in position 1 with the text "gene" and position 3 with the text "UniProtID".

Ah, I think I see what you are after. Is the 5 values in Columns B, C and D always supposed to be the same? If so, I should be able to write code that makes sure that is what is listed within each block. However, your picture does not show the full text that should be in Column B, so if my guess at what you ultimately want is correct, please post the full text for the 5 cells in each of the Columns B, C and D that should appear under each CDS.

 

[susanaidds]

I only need the 5 rows after CDS to be "Gene" "Interferance" "UniprotID" "locus_tag" and "product" and is that in the most of the blocks. When is not, I just need to create the rows for "Gene" and "UniProtID" in collumn B but the other columns should be empty because I need to search the info and put it by hand.

 

[susanaidds]

or, when are more than that, delete the extra ones, that are always the first row or the first and the second

I only need the 5 rows after CDS to be "Gene" "Interferance" "UniprotID" "locus_tag" and "product" and is that in the most of the blocks. When is not, I just need to create the rows for "Gene" and "UniProtID" in collumn B but the other columns should be empty because I need to search the info and put it by hand.

 

[Rick Rothstein]

I only need the 5 rows after CDS to be "Gene" "Interference" "UniprotID" "locus_tag" and "product"

Please clarify the above list... your example table seems to show the word "Interference" twice in Column B whereas you seem to be saying that second "Interference" should be "UniprotID" instead.

 

[susanaidds]

Yes, thats because in the example I didn't treat the table yet. It should be as I discribed

 

[Rick Rothstein]

Okay, this has been lightly tested, but I am pretty sure it will do what you want...

Sub susanaidds()
  Dim Ar As Long, R As Long, X As Long, Rng As Range, Data As Variant, Headers As Variant
  Headers = Split("gene,interference,UniprotID,locus_tag,product", ",")
  With Intersect(Columns("B").SpecialCells(xlConstants).EntireRow, Columns("B:D"))
    For Ar = .Areas.Count To 1 Step -1
      If .Areas(Ar).Rows.Count > 5 Then
        .Areas(Ar).EntireRow.Resize(.Areas(Ar).Rows.Count - 5).Delete
      ElseIf .Areas(Ar).Rows.Count < 5 Then
        .Areas(Ar).EntireRow.Resize(5 - .Areas(Ar).Rows.Count).Insert
      End If
    Next
  End With
  For Each Rng In Columns("A").SpecialCells(xlConstants).Areas
    If Rng.Offset(1, 1).Value = "" Then
      X = 1
      Data = Rng.Offset(1, 1).Resize(5).SpecialCells(xlConstants).Resize(, 3)
      Rng.Offset(1, 1).Resize(5, 3).ClearContents
      For R = 1 To 5
        If X <= UBound(Data) Then
          If LCase(Data(X, 1)) = LCase(Headers(R - 1)) Then
            Rng.Offset(R, 1).Resize(, 3) = Application.Index(Data, X, [{1,2,3}])
            X = X + 1
          Else
            Rng.Offset(R, 1).Value = Headers(R - 1)
          End If
        Else
          Rng.Offset(R, 1).Value = Headers(R - 1)
        End If
      Next
    End If
  Next
End Sub

 

[susanaidds]

I will try in the excel, thank you!