Delete rows from botton up within range if both column C and D cells are blank

[John_356]

Good day,

Provided LRR has been successfully defined as last used row,

I've been trying to find a way to delete all rows within my dynamic range (Range ("C2:D" & LRR)) when both cells from same row on columns C and D are blank.

I'm looking for a short and elegant code here

Many thanks in advance,

John

 

[Fluff]

How about

Sub John()
   On Error Resume Next
   Intersect(Range("C:C").SpecialCells(xlBlanks).EntireRow, Range("D:D").SpecialCells(xlBlanks).EntireRow).Delete
   On Error GoTo 0
End Sub

 

[John_356]

Hi @Fluff ,

Thank you for your input.
I'm afraid that had no effect on my rows ?

 

[Fluff]

That would suggest that either the cells are not truly blank, or you have no rows where both C & D are blank.

 

[Joe4]

Thank you for your input.
I'm afraid that had no effect on my rows

His solution worked fine for me.

I am guessing that your columns C and D may not really be blank.
Are you sure there are spaces or other non-visible characters in them?

Or, are there formulas in those cells which are returning the blanks?
Is so, what exactly are those formulas?

 

[John_356]

Hi @Joe4 , @Fluff ,
Indeed you are both right, thank you for finding the problem, cells are indeed not blank!
Yet they seem blank, no spaces nor formulas... When I test them on the side with an ISBLANK formula the result is indeed FALSE, and if I hit delete on the cell the result changes to TRUE.

Now given cells are visually blank but not really blank, how can I get rid of those poser cells?

 

[Joe4]

Let's find out what is really in one of those cells.
Identify a "blank" cell. Let's say it is cell C6.
Then enter this formula in any blank cell and tell us what it returns:

=CODE(C6)

 

[Fluff]

Try

Sub John()
   With Intersect(ActiveSheet.UsedRange, Range("C:D"))
      .Value = .Value
   End With
   On Error Resume Next
   Intersect(Range("C:C").SpecialCells(xlBlanks).EntireRow, Range("D:D").SpecialCells(xlBlanks).EntireRow).Delete
   On Error GoTo 0
End Sub

 

[John_356]

Okay, great!

When I use the =CODE(C6) formula I get #VALUE!

Now if I write text say "ASD" in C6 just to see what happens and I get a value "65" in return (!)
If I repeat the same with lower case "asd" I get "97" - weird!

 

[Fluff]

Forgot to ask if you have any formulae in C or D.
If you do, don't use the code I posted.