VBA delete n rows if there is a value in cell Ax

[Seb1991]

Hi guys, i need some help with a macro that is probably simple to you, but i don't even know how to explain xD

So i got helped here before with a macro that copies a value from a cell E2 into A2 , if x-y<0 or x-z<0, this every 45 rows.

Option Explicit
Sub Seb1991_V2()
    Dim ws As Worksheet
    Set ws = Worksheets("Foglio1")
    Dim LRow As Long, n As Long
    LRow = WorksheetFunction.Ceiling(ws.Cells(Rows.Count, "E").End(xlUp).Row, 45)
    n = LRow - 45
   
    Dim a, b
    a = ws.Range("E2:L" & LRow)
    ReDim b(1 To UBound(a, 1), 1 To 1)
   
    Dim i As Long
    For i = 1 To LRow - 45 Step 45
   
    Debug.Print a(i + 5, 8)
    Debug.Print a(i + 20, 8)
    Debug.Print a(i + 31, 8)
   
        If a(i, 1) <> "" And (a(i + 5, 8) - a(i + 20, 8) < 0 Or a(i + 5, 8) - a(i + 31, 8) < 0) Then b(i, 1) = a(i, 1)
    Next i
   
    ws.Range("A2").Resize(UBound(b, 1)).Value = b
End Sub

When the value i want is copied in column A, i want to keep 45 rows starting from that value. For example, if i have A2 value, i want to keep all the rows from A2 to A46.
If the value in A47 is not copied, the following 44 rows (so from row 47 to 91) can be deleted.
If there is a value in A92, then keep from A92 to A136 .... i usually have like 20k rows, so as long as it takes.
How can i implement this code into the one i posted ?
Thank you for your help!

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[naigy]

Not quite following your code exactly but I think what you want to do is something similar to this in your final if statement. If I am understanding it correctly you may need to tweak the numbers I have put but hopefully puts you on the right track.

If a(i, 1) <> "" And (a(i + 5, 8) - a(i + 20, 8) < 0 Or a(i + 5, 8) - a(i + 31, 8) < 0) Then 
     b(i, 1) = a(i, 1)
     i = i + 45      'As I am guessing you don't want to check / do anything for the following 45 lines (per your step in the For statement. See comment below
Else
    For i2 = 1 to 44
         Rows(i +1).Delete     'Deletes the row beneath the current (i) row and does this for 44 rows in total. If your wanting to delete 45 rows in total including the current row then replace i+1 with just i and change the For line above to be 45.
    Next i2
End If

Would also need to remove the stepping in for the For Statement. I have basically replaced that with the i = i+45. Otherwise it would cause you issues when you delete the 44 rows as you would then be skipping the next line that in theory if I understand correctly would actually be the next line you want to evaluate against your criteria and could be keeping records you don't want.

Further Note: As you delete lines your final row (ie. actual last row) is going to get less. If you have no data under your records it doesn't really matter but if you do you will want to repeat the LRow Formula after the Next i2 line also as otherwise the macro is going to continue down to what was originally the last row as that was defined before the number of rows was changed.


Hope this helps

 

[Seb1991]

Maybe i found an easier way to do what i want to do, so in my actual code, E2 value (+45+etc) gets copied when L7 is < than L22 or L33 (+45 for each value etc)
it gets copied in A column, so E2 to A2 E 47 to A47 etc, how can i change my code to copy from A2 to A46 ? and every n row after, so like from E47 copied to A47:A91 etc ?

 

[naigy]

Maybe i found an easier way to do what i want to do, so in my actual code, E2 value (+45+etc) gets copied when L7 is < than L22 or L33 (+45 for each value etc)
it gets copied in A column, so E2 to A2 E 47 to A47 etc, how can i change my code to copy from A2 to A46 ? and every n row after, so like from E47 copied to A47:A91 etc ?

So are you not wanting to do validation and literally every 45th line is a value you want and then you want to duplicate that for the next 44 lines and then get the next value and then repeat it again 44 times and so on. To clarify your not wanting to delete records anymore?

If this is the case is column A otherwise empty. If so I would do something similar to the following instead as it is far simplier then having to run a nested for loop (for loop inside another for loop with seperate counting variables).

For i = 2 to Lrow
    If Cells(i, "A").Value = "" Then Cells(i, "A").Value = Cells(i-1, "A").Value
Next i

Otherwise you could try the following which I suspect is maybe more what your after.

For i = 2 To LRow - 45 Step 45
     Cells(i, "A").Value = Cells(i, "E")          'Copies value from "E" row to "A" row in the same row. First instance being Row 2, next loop will start from row 47 (due to above Step 45
     For i2 = i+1 to i+44      'Runs loop from Row 3 to 46)
          Cells(i2, "A").Value = Cells(i,"A")   'Note reference to both "i" and "i2" variables here
     Next i2
Next i

If your wanting to incorporate this with checks to do validation as your original one did then incorporate the above into your main code. Hint: Doing so and running your code on debug so it processes one line at a time can be helpful (you can watch what data is changing and even see what your vba variables are equal to as it progresses.