IO_Dork

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.

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

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
Yes

This macro-enabled workbook has a working example; hope it helps.

29009619-delete-rows-for-blank-cell.xlsm

29009619-delete-rows-for-blank-cell.xlsm

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).

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).

This line does that;

If .Cells(DelRow, 14).Value = "" Or .Cells(DelRow, 16).Value = "" Or .Cells(DelRow, 16).Value < 100000 Then

If .Cells(DelRow, 14).Value = "" Or .Cells(DelRow, 16).Value = "" Or .Cells(DelRow, 16).Value < 100000 Then

ASKER

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

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

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

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

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.

ASKER

Set Ws = ActiveSheet

to this?:

Set Ws = Worksheets("Agencies")