Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Обращение к ячейке скрытого столбца - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Обращение к ячейке скрытого столбца (Макросы/Sub)
Обращение к ячейке скрытого столбца
w00t Дата: Пятница, 20.05.2016, 10:46 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 127
Репутация: 3 ±
Замечаний: 0% ±

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

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

Это действительно в эксель так? И как можно выкрутиться?
 
Ответить
СообщениеВ коде одной из книг использую обращение к ячейкам столбцов.

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

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

Автор - w00t
Дата добавления - 20.05.2016 в 10:46
Hugo Дата: Пятница, 20.05.2016, 10:52 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2671
Репутация: 599 ±
Замечаний: 0% ±

Без проблем можно обратиться:
[vba]
Код
MsgBox Range("B2").Value
[/vba]


excel@nxt.ru
webmoney: E265281470651 R418926282008 Z422237915069
 
Ответить
СообщениеБез проблем можно обратиться:
[vba]
Код
MsgBox Range("B2").Value
[/vba]

Автор - Hugo
Дата добавления - 20.05.2016 в 10:52
_Boroda_ Дата: Пятница, 20.05.2016, 10:53 | Сообщение № 3
Группа: Модераторы
Ранг: Экселист
Сообщений: 10334
Репутация: 4357 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Это действительно в эксель так?

Нет, это не так. Показывайте код и файл


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

Нет, это не так. Показывайте код и файл

Автор - _Boroda_
Дата добавления - 20.05.2016 в 10:53
w00t Дата: Пятница, 20.05.2016, 12:49 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 127
Репутация: 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 - так работает все, потому что не скрывается, только что визуально не красиво...
Ладно, буду ковырять.
 
Ответить
СообщениеНет возможности показать, к сожалению. Много кода и других данных, попробую поковырять еще.

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

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

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

Автор - w00t
Дата добавления - 20.05.2016 в 12:49
Karataev Дата: Пятница, 20.05.2016, 14:20 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 830
Репутация: 311 ±
Замечаний: 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]


 
Ответить
СообщениеПредположу, что причина в скрытых строках, которые скрывает атофильтр. Автофильтр скрывает строки и макрос неправильно определяет последнюю строку, в результате Ваши данные оказываются за пределами определенного макросом диапазона. Наверное, у Вас в макросе последняя строка определяется с помощью "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]

Автор - Karataev
Дата добавления - 20.05.2016 в 14:20
w00t Дата: Пятница, 20.05.2016, 14:54 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 127
Репутация: 3 ±
Замечаний: 0% ±

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

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

Поставил минимальную ширину столбиков вместо их полного скрытия, вот так [vba]
Код
Columns("B:E").ColumnWidth = 0.05
[/vba]
Меньше не поставить. Если поставить меньше - то аналогично [vba]
Код
Columns("B:E").EntireColum.Hidden = True
[/vba] работает.

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


Сообщение отредактировал w00t - Пятница, 20.05.2016, 14:56
 
Ответить
СообщениеДа, все так, понял, в чем дело. Еще здесь почитал

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

Поставил минимальную ширину столбиков вместо их полного скрытия, вот так [vba]
Код
Columns("B:E").ColumnWidth = 0.05
[/vba]
Меньше не поставить. Если поставить меньше - то аналогично [vba]
Код
Columns("B:E").EntireColum.Hidden = True
[/vba] работает.

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

Автор - w00t
Дата добавления - 20.05.2016 в 14:54
_Boroda_ Дата: Пятница, 20.05.2016, 14:58 | Сообщение № 7
Группа: Модераторы
Ранг: Экселист
Сообщений: 10334
Репутация: 4357 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Еще можно у этого "почти скрытого" столбца сделать правую границу белой. Тогда толстая линия не появляется.
В приложенном файле строки 1:14
К сообщению приложен файл: 48637892.xlsm(8Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕще можно у этого "почти скрытого" столбца сделать правую границу белой. Тогда толстая линия не появляется.
В приложенном файле строки 1:14

Автор - _Boroda_
Дата добавления - 20.05.2016 в 14:58
w00t Дата: Пятница, 20.05.2016, 16:09 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 127
Репутация: 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(18Kb)


Сообщение отредактировал w00t - Пятница, 20.05.2016, 16:26
 
Ответить
СообщениеНеобычно, забыл про такое решение, спасибо.

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

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

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

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

Автор - w00t
Дата добавления - 20.05.2016 в 16:09
_Boroda_ Дата: Пятница, 20.05.2016, 16:25 | Сообщение № 9
Группа: Модераторы
Ранг: Экселист
Сообщений: 10334
Репутация: 4357 ±
Замечаний: 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]


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЗря красите границы у ВСЕГО столбца. И красит долго, и потом тормозить будет.
Красьте только у нужного диапазона. Можно добавить еще строк 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]

Автор - _Boroda_
Дата добавления - 20.05.2016 в 16:25
w00t Дата: Пятница, 20.05.2016, 16:40 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 127
Репутация: 3 ±
Замечаний: 0% ±

Спасибо, понял. Красивее справа
[vba]
Код
Range("B1:D25").Borders(xlEdgeRight).ThemeColor = 1
[/vba]
Если не сильно надоел и будет желание ответить еще, то последний вопрос.
Вернуть обратно прежние границы как лучше (не откатить, а заново установить, если даже их небыло ранее)?
[vba]
Код
.ThemeColor = 3
[/vba] их устанавливает, но немного другого цвета.
 
Ответить
СообщениеСпасибо, понял. Красивее справа
[vba]
Код
Range("B1:D25").Borders(xlEdgeRight).ThemeColor = 1
[/vba]
Если не сильно надоел и будет желание ответить еще, то последний вопрос.
Вернуть обратно прежние границы как лучше (не откатить, а заново установить, если даже их небыло ранее)?
[vba]
Код
.ThemeColor = 3
[/vba] их устанавливает, но немного другого цвета.

Автор - w00t
Дата добавления - 20.05.2016 в 16:40
_Boroda_ Дата: Пятница, 20.05.2016, 16:45 | Сообщение № 11
Группа: Модераторы
Ранг: Экселист
Сообщений: 10334
Репутация: 4357 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Красивее справа
Да, я и хотел справа написать. Не то скопипастил.

Вернуть обратно прежние границы
А так?
[vba]
Код
Range("E4:L19").Borders.LineStyle = xlNone
[/vba]


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

Вернуть обратно прежние границы
А так?
[vba]
Код
Range("E4:L19").Borders.LineStyle = xlNone
[/vba]

Автор - _Boroda_
Дата добавления - 20.05.2016 в 16:45
w00t Дата: Пятница, 20.05.2016, 19:45 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 127
Репутация: 3 ±
Замечаний: 0% ±

Все так, отлично, спасибо :)
 
Ответить
СообщениеВсе так, отлично, спасибо :)

Автор - w00t
Дата добавления - 20.05.2016 в 19:45
Michael_S Дата: Суббота, 21.05.2016, 05:13 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1772
Репутация: 337 ±
Замечаний: 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]
только свой № столбца ставить


ЯД: 41001136675053
WM: R389613894253
 
Ответить
Сообщениееще так можно последнюю строку найти:
[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]
только свой № столбца ставить

Автор - Michael_S
Дата добавления - 21.05.2016 в 05:13
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Обращение к ячейке скрытого столбца (Макросы/Sub)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!