Перейти к содержимому
Только с 5 по 18 декабря кредитная карта Tinkoff All Airlines с бесплатным обслуживанием навсегда

VBA Excel. Автоподбор высоты объединенной ячейки

    Автоподбор высоты объединенной ячейки с помощью кода VBA Excel, когда метод AutoFit не работает. Обработка ячеек по списку адресов из массива.

    Автоподбор высоты ячейки

    К сожалению, в объединенных ячейках метод VBA Excel AutoFit не работает. Но есть возможность подогнать ширину или высоту такой ячейки под длину текста с помощью макроса.

    Здесь мы рассмотрим макрос для автоподбора высоты ячейки, объединенной с другими по горизонтали в одной строке, которые обычно используются в заголовках электронных таблиц Excel. Для объединенной ячейки должен быть задан перенос текста по словам: Формат ячеек >> Выравнивание >> переносить текст.

    Высота ячейки будет такой, чтобы уместились все строки, на которые будет разбит контент в зависимости от ширины объединенной ячейки.

    Для решения задачи по автоподбору высоты необходимо с помощью кода VBA определить:

    1. Длину текста (количество символов) в объединенной ячейке.
    2. Ширину объединенной ячейки. Длина одного символа текста со шрифтом и его размером по умолчанию приблизительно соответствует длине символа, в котором измеряется ширина ячейки.
    3. Размер шрифта, чтобы рассчитать коэффициент, увеличивающий или уменьшающий высоту ячейки в зависимости от его (шрифта) размера.

    Макрос VBA Excel для автоподбора высоты ячейки с учетом размера используемого шрифта:

    Переменные:

    • myCell — отдельная ячейка в объединенной;
    • myLen — длина текста в активной ячейке;
    • myWidth — ширина объединенной ячейки;
    • k — коэффициент, вносящий поправку в зависимости от размера шрифта;
    • n — размер шрифта по умолчанию.*

    * Это не точное значение: у меня по умолчанию установлен шрифт Calibri размером 11, но точнее код работает с n = 10. Значение переменной n подбирается опытным путем, так как длина текста зависит от процентного соотношения широких и узких символов, если шрифт не моноширинный. Переменной n можно присваивать и дробные значения для более точного автоподбора высоты.

    Максимальная высота строки — 409,5. Если расчетная высота объединенной ячейки окажется больше, будет сгенерирована ошибка.

    Данный код VBA Excel работает с выделенной ячейкой. Вы можете задать список адресов объединенных ячеек и пройтись макросом по каждой из них.

    Обработка списка ячеек

    Укажите список объединенных ячеек в качестве аргументов функции Array. Для списка используйте адреса только первых ячеек из состава объединенных.

    Переменные:

    • myCell — массив со списком адресов объединенных ячеек;
    • myElem — используется как элемент массива myCell.

    Макрос ObkhodYacheyek по адресам из списка обращается к каждой ячейке по очереди, выделяет ее и запускает код автоподбора высоты PodborVysoty.

    Если выделить диапазон объединенных ячеек по одной, удерживая клавишу Ctrl, то запустить код автоподбора высоты можно с помощью следующего макроса:


    Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

    10 комментариев для “VBA Excel. Автоподбор высоты объединенной ячейки”

    1. Алексей

      Здравствуйте, Евгений. А если сделать проверку каждой строки, ограничив по количеству строк и ячеек, сильно на производительности скажется? Или лучше прописывать
      myCell = Array("A1", ..., "A2000")?

    2. Алексей

      Евгений, подскажите пожалуйста первый вариант, я думаю он длинный, да и я его не осилю.

      1. Евгений

        Да нет, он не длинный, но вряд ли вам подойдут оба варианта, так как замедляет код метод .Select. Я то рассчитывал, когда писал эти коды, на небольшое количество объединенных ячеек. Замерьте время выполнения у этого варианта и напишите, что получилось:

        Запускайте код не из редактора VBA, а кнопкой на рабочем листе или пользовательской форме, так он работает быстрее.

    3. Алексей

      Вводная: у меня документ разноплановый и большой (32 ячейки (ширину подгонял для формата А4) на 2000 строк), т.е. присутствуют строки объединённые в одну (текст многострочный и однострочный), есть таблицы (как однострочные в ячейке, так и многострочные и с картинками в строках и с вертикальным текстом). В основном в строках, это формулы эксель, но есть и просто текст.
      Отчёт по работе макроса. Засечь время не удалось, наблюдал работу макроса. Результат работы:
      1. не правит одинарную строку (на 32 ячейки) многострочный текст, как было до работы макроса, так и осталось;
      2. правит одинарную строку однострочный текст (местами отсутствует эта работа, необходимы ещё тесты, чтобы с уверенностью высказать);
      3. таблицу с однострочными текстами правит хорошо;
      4. в таблице с многострочными текстами есть нюансы, подгоняет ширину под первую ячейку, т.е. если во второй ячейке этой же строки есть более многострочный текст ширина строки определяется по первой ячейке и во второй содержание «съедается»;
      5. вертикальные тексты в ячейках, отсутствуют боковые отступы от грани таблицы до текста;
      6. если есть в документе пустая строка с ячейками не объединённая, макрос её (строку) ширину смещает в ноль;
      Имеются ещё мелкие огрехи, но тут надо ещё тестировать и пробовать.
      Много слов получилось и неутешительных. Если мой пост будет мешать вашей работе, можете его не публиковать. За правками макроса можно будет к вам обращаться?

      1. Евгений

        За небольшими правками можете обращаться. Вот одна из них:

        Это для того, чтобы не скрывались пустые строки.

    4. Алексей

      А по п.1 не правит одинарную строку (на 32 ячейки) многострочный текст, можно сделать правку? или это по другой теме?

      1. Евгений

        Я не совсем понял: многострочный текст в ячейке, объединенной из 32 ячеек?

    5. Алексей

      Описывал процесс и понял, надо проще. Открываем новый документ эксель и ячейку А1 объединяем с ячейкой В1 получим строку (объединённая ячейка А1). Вот в неё вставляем многострочный текст (с переносом слов). Если не менялись размеры стандартных ячеек, то в такую объединённую ячейку в одну строку можно напечатать 17 букв «а». 18 буква сместится её можно будет увидеть только, если расширишь строку. Вот и получается строка одна, а в ней многострочный текст.

      1. Евгений

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

    Добавить комментарий

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