Remove Blanks in Column

[Mcook13]

I have the following formula in the cells in column S is =IF(Key!AY:AY="NotExist", Key!AZ:AZ,"").

I want to remove the blanks and display just these numbers at the top of column S.


Column S

4500429658
4500429343
4500429117
4500434998

<tbody>
</tbody>

 

[Michael M]

this will delete the entire row if the cell in "S" is blank !

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "S").End(xlUp).Row
    For r = lr To 1 Step -1
        If Range("S" & r).Value = "" Then Rows(r).Delete
    Next r
End Sub

 

[Mcook13]

Can you do it in a formula? I don't know enough to do coding

 

[Michael M]

Put this formula in an adjacent column next to first row of your data
Drag down until a blank cell appears as the result
You will need to change the "21" in the formula to whatever the last row of your data is !!

=IFERROR(INDEX($S:$S,SMALL(INDEX(ISBLANK($S$1:$S$21)*10^10+ROW($S$1:$S$21),0),ROW(S1))),"")

 

[Mcook13]

Michael M,

So I did some reading and tried my hand at coding. It didn't work and instead deleted a lot of info. I should of added more detail about my worksheet so you know what I was working with.

I tweaked my sheet so it is more organized and here is what I am trying to do.

I have two columns (columns AZ and BA) on my tab called "Key".

In Column AZ, the cells have either the word "Exist" or "NotExist".

Column BA has a list of part #'s and if they don't appear on separate sheet then in the adjacent cell in Column AZ it will say "NotExist".

I want to display on my main tab called "Orders Dashboard" in column X starting at cell X10, all the orders that were labeled with "NotExist".