Сортировка данных на защищённых листах и в диапазонах с разрывом.

[gamaun]

Здравствуйте, специалисты! Очень нужна Ваша помощь! Есть файлик, на котором листы защищены паролем от изменения и выделения(за исключением некоторых ячеек). На вкладке «рабочие» нужно отсортировать определённый диапазон без снятия защиты. Я попытался записать макрос, но он выполняет сортировку данных только той области которая была выделена при записи. В VBA я ни в зуб ногой, что бы подправить макрос. Лишний бред который записался рекордером я удалил (надеюсь всё и правильно). Остаётся надеяться на Вашу помощь. Надо что бы макрос выполнял сортировку выделяемой области или привязать параметры сортировки к диапазону указанному в ячейках AY4 и AZ4, BA4 и BВ4 и критерию BС4 (естественно значения в ячейках в ручную будут меняться, в зависимости от количества заполненных строк и критерия). На вкладке «ИТР тариф» такая же задача, но там диапазон с разрывом. Заранее благодарен! Прилагаю файл в Excel 2003.

[Шпец Докапыч]

Макрос для листа "РАБОЧИЕ" я почистил ещё, оставив только самое необходимое:

Код Выделить Развернуть

Sub Сортировка_1()
  On Error Resume Next
  ActiveSheet.Unprotect "1234"
  Посл = Application.WorksheetFunction.Max([A:A]) + 5
  Range("B6:AT" & Посл).Sort Range("C6")
  ActiveSheet.Protect "1234"
End Sub

Что касается листа "ИТР ТАРИФ", то, насколько я понимаю, сортировка должна быть одна на оба диапазона? Если такая разбивка предназначена для печати (предполагаю), то может стоит использовать Сквозные строки "$4:$5" (Файл → Параметры страницы → Лист)? Правда придётся что-то придумать с подитогами.

[gamaun]

Уважаемый Шпец Докапыч, большое Вам спасибо за то что согласились мне помочь. Данный макрос выполняет сортировку всего заполненного диапазона. Но таблица заполняется из двух разных списков и поэтому сортировать данные приходится дважды. То есть занесён первый список например в диапазоне В6:АТ47, а второй добавлен ниже в диапазон В48:АТ79, поэтому каждый диапазон нужно сортировать отдельно по столбцу "С". Реально ли так сделать?
На счёт листа "ИТР ТАРИФ" Вы правы, сортировка должна быть одна на оба диапазона В6:AZ30 и В43:AZ67. Сквозные строки я никогда не использовал, честно скажу не знаю как. Из листа "ИТР ТАРИФ" в оригинальном файле вытекает ещё 15 листов, поэтому переделывать его мне кажется будет сложно.

[Шпец Докапыч]

То есть занесён первый список например в диапазоне В6:АТ47, а второй добавлен ниже в диапазон В48:АТ79, поэтому каждый диапазон нужно сортировать отдельно по столбцу "С". Реально ли так сделать?

Тогда будет две строки сортировки: первая по жёсткому диапазону, вторая - до посл. строки, как в моём примере. Если с этим возникнет проблема, то можно опять пройтись макрорекордером и указать где заминка.

На счёт листа "ИТР ТАРИФ" Вы правы, сортировка должна быть одна на оба диапазона В6:AZ30 и В43:AZ67. Сквозные строки я никогда не использовал, честно скажу не знаю как.

Кстати, на листе "РАБОЧИЕ" кто-то всё же использовал сквозные строки. Как-то нелогично получается: раздельная сортировка по цельному диапазону (лист "РАБОЧИЕ") и единая по несмежному (лист "ИТР ТАРИФ"). Второе я реализовал так: скопировал лист, удалил разрыв, отсортировал и скопировал кусочки по местам:

Код Выделить Развернуть

Sub Сортировка_2()
    Set Tf = Sheets("ИТР ТАРИФ")
    On Error Resume Next
    Tf.Unprotect "1234"
    Application.ScreenUpdating = 0
    Tf.Copy Sheets(1)
   
    Rows("31:42").Delete Shift:=xlUp
    Посл = Application.WorksheetFunction.Max(Tf.[A:A]) + 5
    Range("B6:AZ" & Посл).Sort Range("C6")
   
    NoP = Array("B:B", "G:AK", "AO:AO", "AX:AZ")
    For Each cn In NoP
      Tf.Range(cn & " 6:30").Value = Range(cn & " 6:30").Value
      Tf.Range(cn & " 43:" & Посл + 12).Value = _
         Range(cn & " 31:" & Посл).Value
    Next
   
    Application.DisplayAlerts = 0
    ActiveSheet.Delete
    Application.DisplayAlerts = 1
    Tf.Protect "1234"
    Tf.Select
    Application.ScreenUpdating = 1
End Sub

[gamaun]

Спасибо! Попробовать смогу только в понедельник.

[gamaun]

Большое спасибо! Макрос на листе "ИТР тариф" работает отлично! Но пока вернёмся к листу РАБОЧИЕ. Насколько я понял Вы предполагаете что первый диапазон будет жёстким. А в этом как раз и сложность. Величина диапазонов зависит от размеров исходных списков,  у одних граница первого диапазона может быть В6:АТ47, а у других В6:АТ97 или В6:АТ127. Может как то можно диапазон сортировки в макросе привязать к  ячейкам AY4:BB4? Т.е. указал там нужный диапазон, а макрос по нему отсортировал, указал следующий диапазон, нажал на кнопку и опять отсортировал следующий указанный диапазон.

[Шпец Докапыч]

Может как то можно диапазон сортировки в макросе привязать к  ячейкам AY4:BB4?

Если так удобнее, то конечно:

Код Выделить Развернуть

Sub Сортировка_11()
  On Error Resume Next
  ActiveSheet.Unprotect "1234"
  ВД = [AY4] & [AZ4] & ":" & [BA4] & [BB4]
  СС = [BC4] & [AZ4]
  Range(ВД).Sort Range(СС)
  ActiveSheet.Protect "1234"
End Sub

[gamaun]

Спасибо! Может и не очень удобно, зато практично. Какой диапазон пользователь укажет тот и отсортирует! По крайне мере с начало за паролем для сортировки, а потом за исправлениями после блуда "шаловливых ручек" по файлу прибегать больше не будут . А как к листу ИТР тариф приделать этот макрос?

[Шпец Докапыч]

А как к листу ИТР тариф приделать этот макрос?

Аналогичную кнопку прикрутить, которая [ЖМИ СЮДА ДЛЯ СОРТИРОВКИ!!!]. Можно её тупо скопировать, а затем ПКМ → Назначить макрос.

[gamaun]

Аналогичную кнопку прикрутить, которая [ЖМИ СЮДА ДЛЯ СОРТИРОВКИ!!!]. Можно её тупо скопировать, а затем ПКМ → Назначить макрос.

Не в этом загвоздка, с кнопкой проблемы нет. Как быть если сортируемый диапазон получится с разрывом? Я понимаю что из 2-х макросов (Сортировка_11 и Сортировка_2) нужно собрать третий, но как-то надо прописать там, что если указанный диапазон получается с разрывом провести процедуру описанную в макросе "Сортировка_2", а иначе просто идти по пути описанному в макросе "Сортировка_11". Результат совмещения ниже, красным выделил место где по моему мнению надо прописывать условие.
Sub Сортировка_21()
    Set Tf = Sheets("ИТР ТАРИФ")
    On Error Resume Next
    Tf.Unprotect "1234"
    Application.ScreenUpdating = 0
    Tf.Copy Sheets(1)
   
    ВД = [BC4] & [BD4] & ":" & [BE4] & [BF4]
    СС = [BG4] & [BD4]
    Range(ВД).Sort Range(СС)
    Rows("31:42").Delete Shift:=xlUp
   
   
    NoP = Array("B:B", "G:AK", "AO:AO", "AX:BA")
    For Each cn In NoP
      Tf.Range(cn & " 6:30").Value = Range(cn & " 6:30").Value
      Tf.Range(cn & " 43:" & Посл + 12).Value = _
         Range(cn & " 31:" & Посл).Value
    Next
   
    Application.DisplayAlerts = 0
    ActiveSheet.Delete
    Application.DisplayAlerts = 1
    Tf.Protect "1234"
    Tf.Select
    Application.ScreenUpdating = 1
End Sub

[Шпец Докапыч]

Я понимаю что из 2-х макросов (Сортировка_11 и Сортировка_2) нужно собрать третий

Можно составить третий, который по условию будет запускать один из 2-х:

Код Выделить Развернуть

Sub Сортировка2в1()
  rAll = [BB4] - [AZ4] + 1
  СД = [AY4] & [AZ4] & ":" & [AY4] & [BB4]
  rFact = Application.WorksheetFunction.CountA(Range(СД))
  If rFact < rAll Then
        Сортировка_11
  Else: Сортировка_2
  End If
End Sub

[gamaun]

К сожалению этот вариант не подходит, т.к. макрос "Сортировка_2" сортирует полностью весь список, а не выделенный диапазон (указанный в ячейках), а если указать диапазон с разрывом начинается ругня на одну строку макроса(конечно может я не совсем корректно адаптировал к своему листу этот макрос). Мне кажется что надо на основе макроса "Сортировка_11" создавать "Сортировка_21" и что-то придумывать, прописывать условие: если строка, указанная в ячейке 1 меньше 31 и строка указанная в ячейке 2 больше 42, то проводить сортировку на подобие описанной в макросе "Сортировка_2"( с временным удалением строк 31:42....и т.д., но не диапазона В6:посл, а диапазона, указанного в ячейках "1" и "2"), иначе проводить сортировку, как описано в макросе "Сортировка_11". Прикладываю файл где ругается на строку макроса.