значение списка Combobox из динамического диапозона листа эксель

[andreptobts]

Всем добрый день!
Имеется Combobox список которого черпается из динамического диапозона листа эксель. Содержимое динамического диапозона регулируется при помощи Combobox и двух CommandButton "Удалить" и "Добавить" (код ниже)
При удалении последней строки содержащей значение выскакивает ошибка "Run-time error 1004:
mehtod Range of object Global failed
т.к. VBA не может найти по существу исчезнувший диапозон
Вопрос:
1. Как в моем коде (добавить или что-то убавить) можно избежать данной ошибки
2. Можно ли через переменную сделать ссылку на диапозон и уже в коде перебора диапозона прописывать саму переменную
3. Можно ли часть кода где определяются граници диапозона вынести в отдельный модуль и отрабатывать через вызов модуля.

Private Sub Добавить_Click() '
Dim b As Range
Dim x As Variant
Dim y As Variant
For Each b In [Диапозон1] ' перебираем диапозон на листе
If b.Value = Combobox1.Text Then
MsgBox Combobox1.Text & " уже есть всписке"
Exit Sub
Else
End If
Next
' Определяем граници дипозона вставляем новое значение и сортируем
x = Range("Диапозон1").Column
y = Range("Диапозон1").CurrentRegion.Ro ws.Count + 1
Sheets("Лист1").Cells(y, x).Value = Combobox1.Text
ActiveWorkbook.Worksheets("Лист1"). Sort.SortFields.Clear '
ActiveWorkbook.Worksheets("Лист1"). Sort.SortFields.Add Key:=Range(Cells(1, x), Cells(y, x)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Лист1"). Sort
.SetRange Range(Cells(1, x), Cells(y, x))
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Combobox1.RowSource = "Диапозон1"
End Sub


Private Sub Удалить_Click() ' Удаляем из списка
Dim b As Range
Dim x As Variant
Dim y As Variant
For Each b In [Диапозон1] ' сортируем диапозон
If b.Value = Combobox1.Text Then
b.Clear
' Определяем граници дипозона и сортируем
x = Range("Диапозон1").Column
y = Range("Диапозон1").CurrentRegion.Ro ws.Count + 1
ActiveWorkbook.Worksheets("Лист1"). Sort.SortFields.Clear '
ActiveWorkbook.Worksheets("Лист1"). Sort.SortFields.Add Key:=Range(Cells(1, x), Cells(y, x)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Лист1"). Sort
.SetRange Range(Cells(1, x), Cells(y, x))
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Combobox1.RowSource = "Диапозон1"
Exit Sub
Else
End If
Next

End Sub

[IgorGO]

Private Sub Добавить_Click() '
Dim b As Range
Dim x As Variant
Dim y As Variant
For Each b In [Диапозон1] ' перебираем диапозон на листе
If b.Value = Combobox1.Text Then
MsgBox Combobox1.Text & " уже есть всписке"
Exit Sub
Else
End If
Next
' Определяем граници дипозона вставляем новое значение и сортируем
x = Range("Диапозон1").Column
y = Range("Диапозон1").CurrentRegion.Rows.Count + 1
Sheets("Лист1").Cells(y, x).Value = Combobox1.Text
ActiveWorkbook.Worksheets("Лист1").Sort.SortFields.Clear  '
ActiveWorkbook.Worksheets("Лист1").Sort.SortFields.Add Key:=Range(Cells(1, x), Cells(y, x)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Лист1").Sort
.SetRange Range(Cells(1, x), Cells(y, x))
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
With ThisWorkbook.Names("Диапозон1")
  .RefersToR1C1 = "=" & [Диапозон1].Parent.Name & "!R1C" & x & ":R" & y & "C" & x
End With
End Sub


Private Sub Удалить_Click() ' Удаляем из списка
  Dim b As Range
  For Each b In [Диапозон1]
    If b.Value = Combobox1.Text Then
      b.EntireRow.Delete
      Exit Sub
    End If
  Next
End Sub

[andreptobts]

Вставил ваш код и все равно при удаление последней строки выдает ошибку

[IgorGO]

не давайте возможности удалять последнюю строку - не будет ошибки.

удачи!

[andreptobts]

Игорь подскажите, имеется форма на которой куча комббоксов, данные из которого необходимо переносить на лист эксель последовательно и в одну строку. Я это реализую по такому принципу

ActiveCell.Value = box1.Text
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = box2.Text
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = box3.Text
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = box4.Text
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = box5.Text
при наличии около 100 комбобоксов на форме получается ну просто длино
я интуитивно понимаю что можно как то сделать более компактно, но как?

[IgorGO]

как-то так:

for each ctrl in controls
  if ctrl.name like "box*" then
    ...
  end if
next