VBA Help. Alignment of data

[arviy2k]

I have 5 columns of numerical data from Column L to P.
I use the SumIF function in column S to add all the negative numbers, if any.
I then use the IF function in Column R to add the loan # wherever negative values are present.

Here is a screenshot of what it looks like after I'm done with the above:

All I need to do is align this data and move it to the bottom, near the total. This is to make it look presentable and i'll know the defaulting loans at a glance.
I can move it manually, but there are over 50,000 loans and its time consuming.
So, I need a macro to do it, but I'm not sure how to code it.
Below is the screenshot of what it should look like:

Its basically just eliminating the blank cells. Can someone please help me out with the macro code for it?

here is the code i'm using for the SumIf.

Sub addadvances()
'
' addadvances Macro
'

'
    Do Until IsEmpty(ActiveCell.Offset(0, -3)) And IsEmpty(ActiveCell.Offset(1, -3))
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUMIF(RC[-7]:RC[-3],""<0"",RC[-7]:RC[-3])"
    ActiveCell.Copy
    ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell.Offset(-1, 0).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Replace what:="0", replacement:="", LookAt:=xlWhole, searchorder:=xlByRows
    Selection.Cells(1, 1).Select
    ActiveCell.Offset(0, -1).Select
    Application.CutCopyMode = False
    Do Until IsEmpty(ActiveCell.Offset(0, -3)) And IsEmpty(ActiveCell.Offset(1, -3))
    ActiveCell.FormulaR1C1 = "=if(rc[1]<0,rc[-12],"""")"
    ActiveCell.Copy
    ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveCell.Offset(1, 0).Select
    Loop
    
    
End Sub

I've got the addition part right. Just need the macro to delete the blank cells and put the data in one place..

Thanks in advance.
ArviY2k

 

[arviy2k]

Wow that simplified the code. Was really easy to understand. Thank you.

Yes the header row will always be blank.

Does the range have to start with Q4? If I use Q1, then it guarantees blank rows. This will be better as my data may not always start at the 4th row.

Does the code need the selection range to begin at the start of data or can I use the first cell of the worksheet? It worked fine when I tested it using Q1.

All this made me wonder if I should learn VBA systematically and not by mere experimentation. Missing even a period or a parenthesis makes the whole code go haywire.

Regards,
ArviY2k

 

[Peter_SSs]

Using Q1 instead of Q4 should not be a problem from what I can see of your sheet.

 

[arviy2k]

Thanks Peter..

Regards,
ArviY2k