Find blank cell in range
 

I have a variable range, D4: to either- Q, R, S, or T4

I find that range with



lastCol = Range("D4").End(xlToRight).Select



Then I name MyRange in row 5



ActiveCell.Offset(1, 0).Select

Set endcell = ActiveCell

Set MyRange = Range("D5:" &
endcell.Address)



This seems to work (maybe not the best way?)



The cells in MyRange have values that could be from 0 to 100.



I need to search through each cell in MyRange until it finds one that has no
value (blank cell, not a 0 value) If a blank cell is found, I want a msgbox
to tell me.



(I don't even need to know where it is. It just lets me know that all values
are not entered yet and lets me work with the values that are.)



Once one empty cell is found, the msgbox displays, the search stops, and
goes to the rest of my sub. If none is found the sub just continues.



I thank you great people in advance

CR

Find blank cell in range
 

No loop needed:

If Application.WorksheetFunction.CountBlank(MyRange) < 0 Then
MsgBox "at least one blank found"
End If
--
Gary''s Student - gsnu200907


"CR" wrote:

I have a variable range, D4: to either- Q, R, S, or T4

I find that range with



lastCol = Range("D4").End(xlToRight).Select



Then I name MyRange in row 5



ActiveCell.Offset(1, 0).Select

Set endcell = ActiveCell

Set MyRange = Range("D5:" &
endcell.Address)



This seems to work (maybe not the best way?)



The cells in MyRange have values that could be from 0 to 100.



I need to search through each cell in MyRange until it finds one that has no
value (blank cell, not a 0 value) If a blank cell is found, I want a msgbox
to tell me.



(I don't even need to know where it is. It just lets me know that all values
are not entered yet and lets me work with the values that are.)



Once one empty cell is found, the msgbox displays, the search stops, and
goes to the rest of my sub. If none is found the sub just continues.



I thank you great people in advance

CR

Find blank cell in range
 

Put these two functions in your standard module1. Then to use them in your
code:


Dim lc As Long, lr As long
lc = ActiveSheet(Lastcol)
lr = ActiveSheet(lastRow)

Functions below:

Function lastRow(sh As Worksheet) 'Finds last cell with data in the last
used row.
On Error Resume Next
lastRow = sh.Cells.Find(What:="*", After:=sh.Range("A1"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row
On Error GoTo 0
End Function
Function Lastcol(sh As Worksheet) 'Finds last cell with data in the last
used column.
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", After:=sh.Range("A1"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
On Error GoTo 0
End Function



"CR" wrote in message
...
I have a variable range, D4: to either- Q, R, S, or T4

I find that range with



lastCol = Range("D4").End(xlToRight).Select



Then I name MyRange in row 5



ActiveCell.Offset(1, 0).Select

Set endcell = ActiveCell

Set MyRange = Range("D5:" &
endcell.Address)



This seems to work (maybe not the best way?)



The cells in MyRange have values that could be from 0 to 100.



I need to search through each cell in MyRange until it finds one that has
no value (blank cell, not a 0 value) If a blank cell is found, I want a
msgbox to tell me.



(I don't even need to know where it is. It just lets me know that all
values are not entered yet and lets me work with the values that are.)



Once one empty cell is found, the msgbox displays, the search stops, and
goes to the rest of my sub. If none is found the sub just continues.



I thank you great people in advance

CR

Find blank cell in range
 

So simple. My problem was visualizing what was going on.

If I have it right, the 0 is the number of blank cells, not the value 0 that
might be in the cell. Now that I see it, it's easy to see why my different
try's didn't work.

Thank you

"Gary''s Student" wrote in message
...
No loop needed:

If Application.WorksheetFunction.CountBlank(MyRange) < 0 Then
MsgBox "at least one blank found"
End If
--
Gary''s Student - gsnu200907


"CR" wrote:

I have a variable range, D4: to either- Q, R, S, or T4

I find that range with



lastCol = Range("D4").End(xlToRight).Select



Then I name MyRange in row 5



ActiveCell.Offset(1, 0).Select

Set endcell = ActiveCell

Set MyRange = Range("D5:" &
endcell.Address)



This seems to work (maybe not the best way?)



The cells in MyRange have values that could be from 0 to 100.



I need to search through each cell in MyRange until it finds one that has
no
value (blank cell, not a 0 value) If a blank cell is found, I want a
msgbox
to tell me.



(I don't even need to know where it is. It just lets me know that all
values
are not entered yet and lets me work with the values that are.)



Once one empty cell is found, the msgbox displays, the search stops, and
goes to the rest of my sub. If none is found the sub just continues.



I thank you great people in advance

CR