Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Empty Cell Macro
I want to change this macro so that when its finished changing cells B7:B11
it returns to the first cell in the range B7:B11 that does not already have contents inside of it. In other words I want the macro to return to the first empty cell in the range. Sub Order() Range("B7").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B8").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B9").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B10").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B11").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Empty Cell Macro
Call this near the end of your macro:
Sub GoToEmpty() Set r = Range("B7:B11") For Each rr In r If IsEmpty(rr) Then rr.Select Exit Sub End If Next End Sub -- Gary''s Student - gsnu200838 "Workbook" wrote: I want to change this macro so that when its finished changing cells B7:B11 it returns to the first cell in the range B7:B11 that does not already have contents inside of it. In other words I want the macro to return to the first empty cell in the range. Sub Order() Range("B7").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B8").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B9").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B10").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B11").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Empty Cell Macro
You can simplify your macro considerably by doing your Validation statements
against a range instead of each individual cell. I have done this in the code below and, in addition, I have also included code last 2 lines) to select the first empty cell (I set a Range variable named BlankCell to it and then Selected it as I didn't know exactly what you meant by "find" the cell)... Sub OrderThenFindEmptyCell() Dim BlankCell As Range With Range("B7:B11") With .Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Set BlankCell = .Find("", After:=Range("B11"), SearchOrder:=xlByRows) BlankCell.Select End With End Sub -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... Call this near the end of your macro: Sub GoToEmpty() Set r = Range("B7:B11") For Each rr In r If IsEmpty(rr) Then rr.Select Exit Sub End If Next End Sub -- Gary''s Student - gsnu200838 "Workbook" wrote: I want to change this macro so that when its finished changing cells B7:B11 it returns to the first cell in the range B7:B11 that does not already have contents inside of it. In other words I want the macro to return to the first empty cell in the range. Sub Order() Range("B7").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B8").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B9").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B10").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B11").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Empty Cell Macro
You can simplify your macro considerably by doing your Validation statements
against a range instead of each individual cell. I have done this in the code below and, in addition, I have also included code last 2 lines) to select the first empty cell (I set a Range variable named BlankCell to it and then Selected it as I didn't know exactly what you meant by "find" the cell)... Sub OrderThenFindEmptyCell() Dim BlankCell As Range With Range("B7:B11") With .Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Set BlankCell = .Find("", After:=Range("B11"), SearchOrder:=xlByRows) BlankCell.Select End With End Sub -- Rick (MVP - Excel) "Workbook" wrote in message ... I want to change this macro so that when its finished changing cells B7:B11 it returns to the first cell in the range B7:B11 that does not already have contents inside of it. In other words I want the macro to return to the first empty cell in the range. Sub Order() Range("B7").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B8").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B9").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B10").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B11").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Empty Cell Macro
Sorry, I didn't mean to post my response under your subthread... I just
reposted it directly to the OP. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message .. . You can simplify your macro considerably by doing your Validation statements against a range instead of each individual cell. I have done this in the code below and, in addition, I have also included code last 2 lines) to select the first empty cell (I set a Range variable named BlankCell to it and then Selected it as I didn't know exactly what you meant by "find" the cell)... Sub OrderThenFindEmptyCell() Dim BlankCell As Range With Range("B7:B11") With .Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Set BlankCell = .Find("", After:=Range("B11"), SearchOrder:=xlByRows) BlankCell.Select End With End Sub -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... Call this near the end of your macro: Sub GoToEmpty() Set r = Range("B7:B11") For Each rr In r If IsEmpty(rr) Then rr.Select Exit Sub End If Next End Sub -- Gary''s Student - gsnu200838 "Workbook" wrote: I want to change this macro so that when its finished changing cells B7:B11 it returns to the first cell in the range B7:B11 that does not already have contents inside of it. In other words I want the macro to return to the first empty cell in the range. Sub Order() Range("B7").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B8").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B9").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B10").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B11").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Empty Cell Macro
Thank you this is very good.
"Gary''s Student" wrote: Call this near the end of your macro: Sub GoToEmpty() Set r = Range("B7:B11") For Each rr In r If IsEmpty(rr) Then rr.Select Exit Sub End If Next End Sub -- Gary''s Student - gsnu200838 "Workbook" wrote: I want to change this macro so that when its finished changing cells B7:B11 it returns to the first cell in the range B7:B11 that does not already have contents inside of it. In other words I want the macro to return to the first empty cell in the range. Sub Order() Range("B7").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B8").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B9").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B10").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B11").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Empty Cell Macro
O cool, thank you very much. This works awesome!
"Gary''s Student" wrote: Call this near the end of your macro: Sub GoToEmpty() Set r = Range("B7:B11") For Each rr In r If IsEmpty(rr) Then rr.Select Exit Sub End If Next End Sub -- Gary''s Student - gsnu200838 "Workbook" wrote: I want to change this macro so that when its finished changing cells B7:B11 it returns to the first cell in the range B7:B11 that does not already have contents inside of it. In other words I want the macro to return to the first empty cell in the range. Sub Order() Range("B7").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B8").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B9").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B10").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B11").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Empty Cell Macro
Wow! Thank you for taking the time to explain this to me. It works awesome
and your explanation is very helpful to me. "Rick Rothstein" wrote: You can simplify your macro considerably by doing your Validation statements against a range instead of each individual cell. I have done this in the code below and, in addition, I have also included code last 2 lines) to select the first empty cell (I set a Range variable named BlankCell to it and then Selected it as I didn't know exactly what you meant by "find" the cell)... Sub OrderThenFindEmptyCell() Dim BlankCell As Range With Range("B7:B11") With .Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Set BlankCell = .Find("", After:=Range("B11"), SearchOrder:=xlByRows) BlankCell.Select End With End Sub -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... Call this near the end of your macro: Sub GoToEmpty() Set r = Range("B7:B11") For Each rr In r If IsEmpty(rr) Then rr.Select Exit Sub End If Next End Sub -- Gary''s Student - gsnu200838 "Workbook" wrote: I want to change this macro so that when its finished changing cells B7:B11 it returns to the first cell in the range B7:B11 that does not already have contents inside of it. In other words I want the macro to return to the first empty cell in the range. Sub Order() Range("B7").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B8").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B9").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B10").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B11").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Empty Cell Macro
No worries, this is an awesome macro. Thank you!
"Rick Rothstein" wrote: Sorry, I didn't mean to post my response under your subthread... I just reposted it directly to the OP. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message .. . You can simplify your macro considerably by doing your Validation statements against a range instead of each individual cell. I have done this in the code below and, in addition, I have also included code last 2 lines) to select the first empty cell (I set a Range variable named BlankCell to it and then Selected it as I didn't know exactly what you meant by "find" the cell)... Sub OrderThenFindEmptyCell() Dim BlankCell As Range With Range("B7:B11") With .Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Set BlankCell = .Find("", After:=Range("B11"), SearchOrder:=xlByRows) BlankCell.Select End With End Sub -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... Call this near the end of your macro: Sub GoToEmpty() Set r = Range("B7:B11") For Each rr In r If IsEmpty(rr) Then rr.Select Exit Sub End If Next End Sub -- Gary''s Student - gsnu200838 "Workbook" wrote: I want to change this macro so that when its finished changing cells B7:B11 it returns to the first cell in the range B7:B11 that does not already have contents inside of it. In other words I want the macro to return to the first empty cell in the range. Sub Order() Range("B7").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B8").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B9").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B10").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B11").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Empty Cell Macro
I appreciate your explanation very much. Thank you. Your Macro also works
fantastic! "Rick Rothstein" wrote: You can simplify your macro considerably by doing your Validation statements against a range instead of each individual cell. I have done this in the code below and, in addition, I have also included code last 2 lines) to select the first empty cell (I set a Range variable named BlankCell to it and then Selected it as I didn't know exactly what you meant by "find" the cell)... Sub OrderThenFindEmptyCell() Dim BlankCell As Range With Range("B7:B11") With .Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Set BlankCell = .Find("", After:=Range("B11"), SearchOrder:=xlByRows) BlankCell.Select End With End Sub -- Rick (MVP - Excel) "Workbook" wrote in message ... I want to change this macro so that when its finished changing cells B7:B11 it returns to the first cell in the range B7:B11 that does not already have contents inside of it. In other words I want the macro to return to the first empty cell in the range. Sub Order() Range("B7").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B8").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B9").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B10").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Range("B11").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Order" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find empty cells in a column then append row that empty cell is in | Excel Programming | |||
Change Macro to Find First Empty Cell | Excel Discussion (Misc queries) | |||
Macro to find empty cell and select range to print selected. | Excel Programming | |||
Find first empty row in a worksheet with a Macro | Excel Programming | |||
To find empty cell | Excel Programming |