Как макросом изменить границы диапазона

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Как макросом изменить границы диапазона (Макросы/Sub)

Glass4217

Дата: Воскресенье, 17.03.2019, 05:02 | Сообщение № 1

Группа: Пользователи

Ранг: Участник

Сообщений: 55

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Доброго времени суток.

Помогите решить проблему с именованным диапазоном.
Есть таблица C5:E32, она заполнена числами.
Но нижняя граница - которая сейчас проходит по строке 23 - бывает то выше, то ниже.
В зависимости от того где начинается та первая строка, в которой все три значения - 0.
Сейчас все три значения - 0 в строке 24 (C24,D24,E24) ... а значит нижняя граница диапазона должна проходить по строке 23.

Вопрос - как макросом в самом именованном диапазоне - изменить границы диапазона (в зависимости от того, где проходит нижняя его граница) ?

Вот я тут примерно в файле нарисовал - там где одни нули - там красный шрифт, а где числа которые должны входить в диапазон - там зеленым шрифтом подкрасил - чтобы нагляднее было.

К сообщению приложен файл: 657987.xls (31.0 Kb)

Ответить

Anchoret

Дата: Воскресенье, 17.03.2019, 07:29 | Сообщение № 2

Группа: Пользователи

Ранг: Прохожий

Сообщений: 9

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

[vba]

Код

Sub aaa()
Dim aa As Range, dt$, a&, b&, c#
Set aa = [C5].CurrentRegion: a = aa.Rows.Count
Do
  On Error Resume Next
  Do While aa(a, 1) = 0
    a = a - 1
    If Err Then a = a + 1: Exit Do
  Loop
  On Error GoTo 0
  If Application.Sum(aa.Rows(a + 1)) = 0 Then Set aa = aa.Rows("1:" & a): Exit Do
Loop
dt = "=" & ActiveSheet.Name & "!" & aa.Address
ThisWorkbook.Names.Item(1).RefersTo = dt
End Sub

[/vba]

Ответить

Glass4217

Дата: Воскресенье, 17.03.2019, 15:30 | Сообщение № 3

Группа: Пользователи

Ранг: Участник

Сообщений: 55

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Anchoret, что-то не работает.
Добавил столбец F, заполненный числами.
Теперь выдает ошибку.
Подсвечивает строку:
[vba]

Код

If Application.Sum(aa.Rows(a + 1)) = 0 Then

[/vba]

Почему макрос вообще реагирует на столбец F ?
Ведь я же спрашивал про столбцы C,D и E.

К сообщению приложен файл: 657987-2-.xls (43.0 Kb)

Ответить

Nic70y

Дата: Воскресенье, 17.03.2019, 15:52 | Сообщение № 4

Группа: Друзья

Ранг: Экселист

Сообщений: 9006

Репутация: 2369 ±

Замечаний: 0% ±

Excel 2010

Цитата Glass4217, 17.03.2019 в 05:02, в сообщении № 1 ()

Вопрос - как макросом

а чем формула не устраивает?

Код

=СМЕЩ(Лист3!$C$5:$E$5;;;ПОИСКПОЗ("000";Лист3!$C$5:$C$32&Лист3!$D$5:$D$32&Лист3!$E$5:$E$32;)-1)

К сообщению приложен файл: 1207946.xls (29.0 Kb)

ЮMoney 41001841029809

Ответить

Glass4217

Дата: Воскресенье, 17.03.2019, 16:36 | Сообщение № 5

Группа: Пользователи

Ранг: Участник

Сообщений: 55

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Nic70y,потому что формула работает постоянно при каждом пересчете листа.
А мне нужно однократное срабатывание при одном нажатии кнопки макроса.

К тому же в вашем решении - пустует место значений, хотя там должны отображаться числа.
Сейчас же там - написано : {...}

Сообщение отредактировал Glass4217 - Воскресенье, 17.03.2019, 16:44

Ответить

Nic70y

Дата: Воскресенье, 17.03.2019, 16:48 | Сообщение № 6

Группа: Друзья

Ранг: Экселист

Сообщений: 9006

Репутация: 2369 ±

Замечаний: 0% ±

Excel 2010

Цитата Glass4217, 17.03.2019 в 16:36, в сообщении № 5 ()

К тому же в вашем решении - пустует место значений

не понял, о чем Вы.

К сообщению приложен файл: 6014142.gif (6.6 Kb)

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Воскресенье, 17.03.2019, 16:48

Ответить

Glass4217

Дата: Воскресенье, 17.03.2019, 17:02 | Сообщение № 7

Группа: Пользователи

Ранг: Участник

Сообщений: 55

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Nic70y, я вот о чем:

Ответить

Glass4217

Дата: Воскресенье, 17.03.2019, 17:23 | Сообщение № 8

Группа: Пользователи

Ранг: Участник

Сообщений: 55

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Anchoret, и еще вопрос - а этот код - какой именно именованный диапазон меняет (если таких диапазонов несколько) ?

В коде сейчас - нигде не отображается название именованного диапазона.

Ответить

Pelena

Дата: Воскресенье, 17.03.2019, 17:39 | Сообщение № 9

Группа: Админы

Ранг: Местный житель

Сообщений: 19405

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

Ну, если формулой не нравится, то можно так попробовать
[vba]

Код

Sub aaa()
    Dim aa As Range, a&, i&
    Set aa = [C5].CurrentRegion: a = aa.Rows.Count
    For i = 1 To a
        If Application.Sum(aa.Cells(i, 1).Resize(, 3)) = 0 Then
            ThisWorkbook.Names("Диапазон1").RefersTo = "=" & ActiveSheet.Name & "!$C$5:$E$" & i + 3: Exit For
        End If
    Next i
End Sub

[/vba]

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Nic70y

Дата: Воскресенье, 17.03.2019, 17:59 | Сообщение № 10

Группа: Друзья

Ранг: Экселист

Сообщений: 9006

Репутация: 2369 ±

Замечаний: 0% ±

Excel 2010

Цитата Glass4217, 17.03.2019 в 17:02, в сообщении № 7 ()

я вот о чем

та это ерунда.
ну и поприколу:
[vba]

Код

Sub u__()
u = Evaluate("=MATCH(""000"",C:C&D:D&E:E, 0)")
ThisWorkbook.Names("Диапазон1").RefersTo = Sheets("Лист3").Range("C5:E" & u - 1)
End Sub

[/vba]

ЮMoney 41001841029809

Ответить

Anchoret

Дата: Воскресенье, 17.03.2019, 19:36 | Сообщение № 11

Группа: Пользователи

Ранг: Прохожий

Сообщений: 9

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Glass4217, на будущее - файл-пример должен отражать реальную структуру данных. Если именованных диапазонов несколько ,то стоит указать и это.
[vba]

Код

Sub aaa()
Dim aa As Range, a&, dt$
a = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Set aa = Intersect(Rows("5:" & a), Columns("C:E")): a = aa.Rows.Count
Do
  On Error Resume Next
  Do While aa(a, 1) = 0
    a = a - 1
    If Err Then a = a + 1: Exit Do
  Loop
  On Error GoTo 0
  If Application.Sum(aa.Rows(a + 1)) = 0 Then Set aa = aa.Rows("1:" & a): Exit Do Else a = a - 1
Loop
On Error Resume Next: dt = ThisWorkbook.Names("Диапазон1").RefersTo
If Err Then
  ThisWorkbook.Names.Add "Диапазон1", "=" & ActiveSheet.Name & "!" & aa.Address
Else: ThisWorkbook.Names("Диапазон1").RefersTo = "=" & ActiveSheet.Name & "!" & aa.Address
End If: Err.Clear
End Sub

[/vba]
Если вдруг именованные диапазоны куда-то подевались, то макрос добавит.

Сообщение отредактировал Anchoret - Понедельник, 18.03.2019, 00:34

Ответить

Glass4217

Дата: Воскресенье, 17.03.2019, 19:49 | Сообщение № 12

Группа: Пользователи

Ранг: Участник

Сообщений: 55

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Anchoret, вроде бы все работает.

Но макрос почему-то захватывает одну строчку сверху, если в ней - что-то есть.
Например когда я размещаю строчкой выше - заголовок - то макрос включает в именованный диапазон и этот заголовок (то есть с четвертой строки его начинает, а не с пятой).
Хотя вроде бы - в макросе записано C5 - то есть обозначение верхней границы диапазона (пятая строка).

Почему так происходит и как это поменять ?