Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find last non-blank cell in range | Excel Discussion (Misc queries) | |||
Find non blank cell in range and fill the rest with that value | Excel Programming | |||
How do I find the next blank cell in a range? | Excel Worksheet Functions | |||
find the first blank cell in a range and return me it's position | Links and Linking in Excel | |||
How to find the first blank cell in a range | Excel Programming |