Автоподбор высоты объединенной ячейки с помощью кода VBA Excel, когда метод AutoFit не работает. Обработка ячеек по списку адресов из массива.
Автоподбор высоты ячейки
К сожалению, в объединенных ячейках метод VBA Excel AutoFit не работает. Но есть возможность подогнать ширину или высоту такой ячейки под длину текста с помощью макроса.
Здесь мы рассмотрим макрос для автоподбора высоты ячейки, объединенной с другими по горизонтали в одной строке, которые обычно используются в заголовках электронных таблиц Excel. Для объединенной ячейки должен быть задан перенос текста по словам: Формат ячеек >> Выравнивание >> переносить текст.
Высота ячейки будет такой, чтобы уместились все строки, на которые будет разбит контент в зависимости от ширины объединенной ячейки.
Для решения задачи по автоподбору высоты необходимо с помощью кода VBA определить:
- Длину текста (количество символов) в объединенной ячейке.
- Ширину объединенной ячейки. Длина одного символа текста со шрифтом и его размером по умолчанию приблизительно соответствует длине символа, в котором измеряется ширина ячейки.
- Размер шрифта, чтобы рассчитать коэффициент, увеличивающий или уменьшающий высоту ячейки в зависимости от его (шрифта) размера.
Макрос VBA Excel для автоподбора высоты ячейки с учетом размера используемого шрифта:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Sub PodborVysoty() Dim myCell As Range, myLen As Integer, _ myWidth As Single, k As Single, n As Single With Selection 'Задаем объединенной ячейке перенос текста .WrapText = True 'Задаем объединенной ячейке такую высоту строки, 'чтобы умещалась одна строка текста .RowHeight = ActiveCell.Font.Size * 1.3 End With myLen = Len(CStr(ActiveCell)) For Each myCell In Selection myWidth = myWidth + myCell.ColumnWidth Next n = 10 k = ActiveCell.Font.Size / n Selection.RowHeight = Selection.RowHeight * _ WorksheetFunction.RoundUp(myLen * k / myWidth, 0) End Sub |
Переменные:
- myCell — отдельная ячейка в объединенной;
- myLen — длина текста в активной ячейке;
- myWidth — ширина объединенной ячейки;
- k — коэффициент, вносящий поправку в зависимости от размера шрифта;
- n — размер шрифта по умолчанию.*
* Это не точное значение: у меня по умолчанию установлен шрифт Calibri размером 11, но точнее код работает с n = 10. Значение переменной n подбирается опытным путем, так как длина текста зависит от процентного соотношения широких и узких символов, если шрифт не моноширинный. Переменной n можно присваивать и дробные значения для более точного автоподбора высоты.
Максимальная высота строки — 409,5. Если расчетная высота объединенной ячейки окажется больше, будет сгенерирована ошибка.
Данный код VBA Excel работает с выделенной ячейкой. Вы можете задать список адресов объединенных ячеек и пройтись макросом по каждой из них.
Обработка списка ячеек
Укажите список объединенных ячеек в качестве аргументов функции Array. Для списка используйте адреса только первых ячеек из состава объединенных.
|
1 2 3 4 5 6 7 8 9 |
Sub ObkhodYacheyek1() Sub ObkhodYacheyek() Dim myCell() As Variant, myElem As Variant myCell = Array("A1", "A3", "A5") For Each myElem In myCell Range(myElem).Select Call PodborVysoty Next End Sub |
Переменные:
- myCell — массив со списком адресов объединенных ячеек;
- myElem — используется как элемент массива myCell.
Макрос ObkhodYacheyek по адресам из списка обращается к каждой ячейке по очереди, выделяет ее и запускает код автоподбора высоты PodborVysoty.
Если выделить диапазон объединенных ячеек по одной, удерживая клавишу Ctrl, то запустить код автоподбора высоты можно с помощью следующего макроса:
|
1 2 3 4 5 6 7 8 |
Sub ObkhodYacheyek2() Dim myCell() As String, myElem As Variant myCell = Split(Selection.Address, ",") For Each myElem In myCell Range(myElem).Select Call PodborVysoty Next End Sub |
Здравствуйте, Евгений. А если сделать проверку каждой строки, ограничив по количеству строк и ячеек, сильно на производительности скажется? Или лучше прописывать
myCell = Array("A1", ..., "A2000")?Добрый день, Алексей!
Сравните время выполнения обоих вариантов вашего кода.
Евгений, подскажите пожалуйста первый вариант, я думаю он длинный, да и я его не осилю.
Да нет, он не длинный, но вряд ли вам подойдут оба варианта, так как замедляет код метод
.Select. Я то рассчитывал, когда писал эти коды, на небольшое количество объединенных ячеек. Замерьте время выполнения у этого варианта и напишите, что получилось:Запускайте код не из редактора VBA, а кнопкой на рабочем листе или пользовательской форме, так он работает быстрее.
Вводная: у меня документ разноплановый и большой (32 ячейки (ширину подгонял для формата А4) на 2000 строк), т.е. присутствуют строки объединённые в одну (текст многострочный и однострочный), есть таблицы (как однострочные в ячейке, так и многострочные и с картинками в строках и с вертикальным текстом). В основном в строках, это формулы эксель, но есть и просто текст.
Отчёт по работе макроса. Засечь время не удалось, наблюдал работу макроса. Результат работы:
1. не правит одинарную строку (на 32 ячейки) многострочный текст, как было до работы макроса, так и осталось;
2. правит одинарную строку однострочный текст (местами отсутствует эта работа, необходимы ещё тесты, чтобы с уверенностью высказать);
3. таблицу с однострочными текстами правит хорошо;
4. в таблице с многострочными текстами есть нюансы, подгоняет ширину под первую ячейку, т.е. если во второй ячейке этой же строки есть более многострочный текст ширина строки определяется по первой ячейке и во второй содержание «съедается»;
5. вертикальные тексты в ячейках, отсутствуют боковые отступы от грани таблицы до текста;
6. если есть в документе пустая строка с ячейками не объединённая, макрос её (строку) ширину смещает в ноль;
Имеются ещё мелкие огрехи, но тут надо ещё тестировать и пробовать.
Много слов получилось и неутешительных. Если мой пост будет мешать вашей работе, можете его не публиковать. За правками макроса можно будет к вам обращаться?
За небольшими правками можете обращаться. Вот одна из них:
Это для того, чтобы не скрывались пустые строки.
А по п.1 не правит одинарную строку (на 32 ячейки) многострочный текст, можно сделать правку? или это по другой теме?
Я не совсем понял: многострочный текст в ячейке, объединенной из 32 ячеек?
Описывал процесс и понял, надо проще. Открываем новый документ эксель и ячейку А1 объединяем с ячейкой В1 получим строку (объединённая ячейка А1). Вот в неё вставляем многострочный текст (с переносом слов). Если не менялись размеры стандартных ячеек, то в такую объединённую ячейку в одну строку можно напечатать 17 букв «а». 18 буква сместится её можно будет увидеть только, если расширишь строку. Вот и получается строка одна, а в ней многострочный текст.
Если у вас автоматически не устанавливается высота строки, когда только один или несколько символов перенесены на новую строку, подберите более точное значение коэффициента
n.Привет.
Я придумал метод, как подстроить высоту объединенной ячейки с длинным текстом с помощью AutoFit, который встроен в Excel.
Идея заключается в том, чтобы на пустом (новом) листе завести обычную ячейку (формат естественно с переносом текста).
Если в нее скопировать текст из исходной объединенной ячейки, то он автоматом разобъется на строки и высота этой конечной ячейки даст нам искомую высоту для такой же объединенной ячейки.
Проблема в том, чтобы подобрать ширину одиночной ячейки такую, чтобы текст из объединенной ячейки «укладывался» в нее аналогичным образом. Ибо, как выяснилось в процессе эта ширина хоть и зависит линейно от ширины исходной ячейке, но не равна ей.
Например.
У меня есть объединенная ячейка, которая получилась объединением 62 одинаковых ячеек шириной 1.43 пункта.
Шрифт Coliri, 11, обычный.
«Ширина» объединенной ячейки получается 1.43 х 62 = 88.6
Так вот для обычной ячейки этой ширины оказалось мало.
Чтобы текст из исходной ячейки уложился в обычной в те же 5 строк, потребовалось задать ширину конечной ячейки 139.
То есть при выборе ширины обычной конечной ячейки нужно применять коэффициент 1.55-1.56 (в моем случае).
Возможно этот коэффициент постоянный, возможно зависит как он выбранного шрифта, так и от количества объединяемых ячеек и их исходной ширины и моноширинности (этот вопрос я не исследовал).
Но в итоге метод работает.
Я завожу обычную ячейку нужной ширины записываю в нее текст из исходной объединенной, текст автопереносится, и я получаю высоту для объединенной ячейки из свойств обычной.
Существенное замечание.
Формат ячейки для работы с длинным текстом должен быть "Общий" ("General").
При записи же в ячейку с форматом "Текстовый" строки более 256 символов отображаются ############ и автофит не работает.
Если формат Общий, то все работает и отображается как нужно.