Find the Empty Cell Macro

[Workbook]

I want to change this macro so that when it’s 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

 

[Gary''s Student]

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

 

[Rick Rothstein]

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 Rothstein]

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 Rothstein]

Sorry, I didn't mean to post my response under your subthread... I just
reposted it directly to the OP.

 

[MCheru]

Thank you this is very good.

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

 

[Workbook]

O cool, thank you very much. This works awesome!

 

[Workbook]

Wow! Thank you for taking the time to explain this to me. It works awesome
and your explanation is very helpful to me.

 

[Workbook]

No worries, this is an awesome macro. Thank you!

 

[Workbook]

I appreciate your explanation very much. Thank you. Your Macro also works
fantastic!