Обращение к ячейке скрытого столбца

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

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

Мир MS Excel » Вопросы и решения » Вопросы по VBA » Обращение к ячейке скрытого столбца (Макросы/Sub)

w00t

Дата: Пятница, 20.05.2016, 10:46 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 131

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

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

В коде одной из книг использую обращение к ячейкам столбцов.

Обнаружил неприятный момент. Если столбцы, к которым происходит обращение (Range("B2").Value и отдельно к Range("B3").Value)- скрываю через VBA, то участок кода, который ссылается на ячейки скрытых столбцов - не выполняется корректно.

Это действительно в эксель так? И как можно выкрутиться?

Ответить

Hugo

Дата: Пятница, 20.05.2016, 10:52 | Сообщение № 2

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

Ранг: Участник клуба

Сообщений: 3252

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

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

2019

Без проблем можно обратиться:
[vba]

Код

MsgBox Range("B2").Value

[/vba]

excel@nxt.ru
webmoney: E265281470651 Z422237915069

Ответить

w00t

Дата: Пятница, 20.05.2016, 12:49 | Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 131

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

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

Нет возможности показать, к сожалению. Много кода и других данных, попробую поковырять еще.

Попробую еще проинспектировать весь код. И только в одном случае не работает:
0. Столбцы B:E - в них данные, на которые ссылается код, при отработке на изменение листа;
1. Устанавливаю Columns("B:E").ColumnWidth = 0 или Columns("B:E").EntireColum.Hidden = True
2. Установлен автофильтр и стоит фильтр по какому то значению.

В таком случае меняю данные на листе и код просто не выполняется. Но если автофильтра нет - то все ок.

PS: Columns("B:E").ColumnWidth = 0.1 - так работает все, потому что не скрывается, только что визуально не красиво...
Ладно, буду ковырять.

Ответить

Karataev

Дата: Пятница, 20.05.2016, 14:20 | Сообщение № 4

Группа: Проверенные

Ранг: Старожил

Сообщений: 1334

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

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

Excel

Предположу, что причина в скрытых строках, которые скрывает атофильтр. Автофильтр скрывает строки и макрос неправильно определяет последнюю строку, в результате Ваши данные оказываются за пределами определенного макросом диапазона. Наверное, у Вас в макросе последняя строка определяется с помощью "End(xlUp)", который не работает со скрытыми строками. Например, это может выглядеть так:
[vba]

Код

Sub Макрос1()
Dim lr As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
End Sub

[/vba]
Для определения последней строки используйте что-нибудь другое. Например, можно использовать метод "Find" с параметром "LookIn:=xlFormulas" (этот параметр позволяет искать в скрытых строках). Например, такой код:
[vba]

Код

Sub Макрос2()
    Dim lr As Long
    lr = Columns("B").Find(What:="*", LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
        , SearchFormat:=False).Row
End Sub

[/vba]

Ответить

w00t

Дата: Пятница, 20.05.2016, 14:54 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 131

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

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

Да, все так, понял, в чем дело. Еще здесь почитал

Но, в моем случае самый оптимальный вариант, как ни прискорбно - оставить как есть.

Поставил минимальную ширину столбиков вместо их полного скрытия, вот так [vba]

Код

Columns("B:E").ColumnWidth = 0.05

[/vba]
Меньше не поставить. Если поставить меньше - то аналогично [vba]

Код

Columns("B:E").EntireColum.Hidden = True

[/vba] работает.

Эстетически лучше было бы полностью скрыть столбы, но так тоже неплохо.

Сообщение отредактировал w00t - Пятница, 20.05.2016, 14:56

Ответить

_Boroda_

Дата: Пятница, 20.05.2016, 14:58 | Сообщение № 6

Группа: Модераторы

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

Сообщений: 16674

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

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

2003; 2007; 2010; 2013 RUS

Еще можно у этого "почти скрытого" столбца сделать правую границу белой. Тогда толстая линия не появляется.
В приложенном файле строки 1:14

К сообщению приложен файл: 48637892.xlsm (8.0 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

w00t

Дата: Пятница, 20.05.2016, 16:09 | Сообщение № 7

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 131

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

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

Необычно, забыл про такое решение, спасибо.

Вышло правда, монстрообразно по коду (просто столбики скрываются и открываются в зависимости от юзера). Потому сделал пока что в "одну сторону" - убрал границы. Если другой юзер с файликом будет работать - то в обратную сторону придется сделать - восстановить границы))

И после удаления границ выделение остается диапазона.

Но это лучше гораздо, чем раньше было.

[vba]

Код

Sub Macro1()
    Columns("B:D").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

[/vba]

К сообщению приложен файл: 0330238.xlsm (18.2 Kb)

Сообщение отредактировал w00t - Пятница, 20.05.2016, 16:26

Ответить

_Boroda_

Дата: Пятница, 20.05.2016, 16:25 | Сообщение № 8

Группа: Модераторы

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

Сообщений: 16674

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

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

2003; 2007; 2010; 2013 RUS

Зря красите границы у ВСЕГО столбца. И красит долго, и потом тормозить будет.
Красьте только у нужного диапазона. Можно добавить еще строк 40-50 снизу. Или просто лишние строки снизу скрывайте.
Примерно вот так
[vba]

Код

Sub Macro1()
    Application.ScreenUpdating = 0
    Range("B1:D25").Borders(xlEdgeLeft).ThemeColor = 1
    Range("B1:D25").Borders(xlInsideVertical).ThemeColor = 1
    Range("A26:A" & Rows.Count).RowHeight = 0'скрытие снизу, не обязательно
End Sub

[/vba]

Ответить

w00t

Дата: Пятница, 20.05.2016, 16:40 | Сообщение № 9

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 131

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

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

Спасибо, понял. Красивее справа
[vba]

Код

Range("B1:D25").Borders(xlEdgeRight).ThemeColor = 1

[/vba]
Если не сильно надоел и будет желание ответить еще, то последний вопрос.
Вернуть обратно прежние границы как лучше (не откатить, а заново установить, если даже их небыло ранее)?
[vba]

Код

.ThemeColor = 3

[/vba] их устанавливает, но немного другого цвета.

Ответить

_Boroda_

Дата: Пятница, 20.05.2016, 16:45 | Сообщение № 10

Группа: Модераторы

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

Сообщений: 16674

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

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

2003; 2007; 2010; 2013 RUS

Цитата w00t, 20.05.2016 в 16:40, в сообщении № 10

Красивее справа

Да, я и хотел справа написать. Не то скопипастил.

Цитата w00t, 20.05.2016 в 16:40, в сообщении № 10

Вернуть обратно прежние границы

А так?
[vba]

Код

Range("E4:L19").Borders.LineStyle = xlNone

[/vba]

Ответить

w00t

Дата: Пятница, 20.05.2016, 19:45 | Сообщение № 11

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 131

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

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

Все так, отлично, спасибо

Ответить

Michael_S

Дата: Суббота, 21.05.2016, 05:13 | Сообщение № 12

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

Ранг: Старожил

Сообщений: 2012

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

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

Excel2016

еще так можно последнюю строку найти:
[vba]

Код

For Each c In Range(Cells(Rows.Count, 1).End(xlUp), Cells(Rows.Count, 1))
        If Not c.Offset(1).EntireRow.Hidden Then
            R = c.Row
            Exit For
        End If
   Next

[/vba]
только свой № столбца ставить

Ответить

Мир MS Excel » Вопросы и решения » Вопросы по VBA » Обращение к ячейке скрытого столбца (Макросы/Sub)

Страница 1 из 1
1