asked on

Macro to delete rows where col 14 and 16 contain blank cells

I am looking for an excel macro that will first sort my worksheet by Column R (ascending order), then look for any blank formula results in Column N or formula result in Column P < 100,000, and delete those rows. For example, if formula in N54 returns no result, then delete row 54, same for Column P. Then finish Macro by selecting cell A2.

Data to be evaluated and sorted is in range A31:AT1000, please note that table headers are on A31.

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

IO_Dork

ASKER

so your saying that I would change this line:
Set Ws = ActiveSheet

to this?:
Set Ws = Worksheets("Agencies")

Shums Faruk

Yes

Alan Varga

This macro-enabled workbook has a working example; hope it helps.
29009619-delete-rows-for-blank-cell.xlsm

IO_Dork

ASKER

Shums:

ok, so it works well. However, for Column P, it has to delete rows if formula result in P is either less than 100,000 or P results in " ", (no result, null).

Shums Faruk

This line does that;
If .Cells(DelRow, 14).Value = "" Or .Cells(DelRow, 16).Value = "" Or .Cells(DelRow, 16).Value < 100000 Then

IO_Dork

ASKER

your right...i had "-" in the formula instead of " ", to return nothing.

Shums Faruk

Do you want me to add "-" too for Column P?

Shums Faruk

change this line
If .Cells(DelRow, 14).Value = "" Or .Cells(DelRow, 16).Value = "" Or .Cells(DelRow, 16).Value < 100000
to
If .Cells(DelRow, 14).Value = "" Or .Cells(DelRow, 16).Value = "" Or .Cells(DelRow, 16).Value = "-" Or .Cells(DelRow, 16).Value < 100000

IO_Dork

ASKER

look like its working...but I have one more part of it to test for correctness. I have to leave the office...I will finalize tomorrow morning and close out this question. Thanks.