detecting empty cell in rows over 150 worksheets

[sl1990]

Hi ive got 150 sheets and in each i have data in range b1:r1, b4:r4, b6:r6, b8:r8, ect. (the sheets are named sheet1-150)

some of sheets only have 3 ranges of data others have 15. The one thing they all have in common is there is always something in the b column of the range (if there is data in that row)

i need to do this for a macro i want to run after i need to remove sheets that have any empty cells in these ranges

dose any body know of a macro that would work that if cell b(row number) is got data in it then check if relivant range has any empty cells in it.

also how would this be information be returned. could it paste into another sheet called say results ?


sorry if i havnt explained this well

thanks

 

[sl1990]

hi sorry ive been away for a day, had somthing realy important to deal with.

thanks so much for helping with this so far, i thought to clarify id show you this google doc
https://spreadsheets.google.com/spr...EFCTW9jd1hOYzZ1cjdwUEZ1TXZMS1E&hl=en_US&gid=1

basicly i want to find any empty cells in any other the colored rows range B:R, i want to do this so i can run the following macro after

Sub sportsRank_1()

Range("X3").Value = "Place"
Range("Y3").Value = "Name(s)"
Range("X4").Value = "1st"
Range("Y4:Y100").Value = ""
On Error Resume Next
For Each cell In Range("B4:R4")
cell.Offset(1, 0).Value = Application.Rank(cell.Value, Range("B4:R4"), 0)
Next
For i = 1 To 10
For Each cell In Range("B5:R5")
If cell.Value = i Then
setPlace = setPlace & cell.Offset(-2, 0).Value & ", "
End If
Next cell
Range("Y" & Rows.Count).End(xlUp).Offset(1, 0).Value = Left(setPlace, Len(setPlace) - 2)
setPlace = ""
Next i
Range("B5:R5").ClearContents
Range("Y5:Y100").ClearContents

End Sub

this is only one part of the macro but all it dose is repeat for the colored lines below it .

ill run this to determin the consistently best scoring person (higher score better) but this macro dose not work if there are empty cells in the range it is working on it returns an empty cell as a best score. is there any way i could change the above macro so that if it finds an empty cell it returns the words "EMPTY" in the Y collom along with the result so after i could just use the standard edit>find to find all the sheets with these empty cells ??

thanks in advance !