shortening a vba code

[iceblocks]

Hi all,
I have written a code and it works fine, but for my learning purposes is there a way to simplify this code...

If Len(Range("J10")) = 0 Or Len(Range("J14")) = 0 Or Len(Range("J29")) = 0 Or Len(Range("L10")) = 0 Or Len(Range("L14")) = 0 Or Len(Range("L29")) = 0 Then

Many thanks.
iceblocks

 

[Hui]

Iceblocks

setup a named formula as
myRng: =Sheet1!$J$10,Sheet1!$L$10,Sheet1!$J$14,Sheet1!$L$14,Sheet1!$J$29,Sheet1!$L$29

Then in VBA

If Application.WorksheetFunction.CountA([MyRng]) <> 6 Then ...

 

[NARAYANK991]

Hi ,

As an alternative you can do this within VBA itself :

          Dim Rng As Range
          Set Rng = Union(Range("J10"), Range("J14"), Range("J29"), Range("L10"), Range("L14"), Range("L29"))
          If Application.WorksheetFunction.CountA(Rng) <> Rng.Cells.Count Then MsgBox "At least one blank cell"

Narayan

 

[iceblocks]

Thank you Hui and Narayank991, really appreciate you guys helping me.