Macro to delete rows above a certain cell

[alamode]

I need a macro to do this:

1. Search in Coloumn T for any cell with the format top and double bottom border (as mentioned in the borders list)
2. Once the cell has been found, count the number of blank spaces above it. So if lets say the cell with the top and double bottom border is cell T50 and there are lets say 8 blanks above it.. which means T41 contains some value.
3. Once it has counted the empty blank cells, delete the rows above it so that there is only 1 blank row above it.

I hope it makes sense.. Can someone help me !

 

[gaj104]

Give this a go:

Sub CleanUp()

Dim i As Long, myRows As Long

For i = Range("T:T").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row To 1 Step -1

    If Range("T" & i).Borders(xlEdgeTop).LineStyle = xlContinuous And Range("T" & i).Borders(xlEdgeBottom).LineStyle = xlDouble Then
    
        myRows = Range("T" & i).End(xlUp).Row
        Range(myRows & ":" & i - 2).Delete
        i = myRows
    
    End If

Next i

End Sub

Take a copy of your sheet and test first.

Hope it helps.

 

[alamode]

Works ! but there are multiple instances of it in the coloumn. In some cases there are like 4-5, In some cases there are only 2. So i need it to repeat..

Uhh.. Wait. it does work multiple times :$.

I guess im having a problem because the top double bottom cell, There is one instance in which there is one of those formatted cells on T52 and another on T55. It runs the code and it deletes the one on T52 and the one on T55 ends up on T45.. I hope im making sense !

Ah forget it.. I fixed it !! Thanks alot !

 

[alamode]

Having an issue with it.. It deletes the last row with data above the single and double bottom border cell.