Автоподбор ширины объединенной ячейки с помощью кода VBA Excel, когда метод AutoFit не работает. Обработка ячеек по списку адресов из массива.
Автоподбор ширины ячейки
К сожалению, в объединенных ячейках метод VBA Excel AutoFit не работает. Но есть возможность подогнать ширину или высоту такой ячейки под длину текста с помощью макроса.
Здесь мы рассмотрим макрос для автоподбора ширины ячейки, объединенной с другими по горизонтали в одной строке, которые обычно используются в заголовках электронных таблиц Excel. Ширина столбцов, пересекающихся с объединенной ячейкой, будет одинаковой. Код работает и с необъединенными ячейками.
Для решения задачи по автоподбору ширины необходимо с помощью кода VBA определить:
- Длину текста (количество символов). Длина одного символа текста со шрифтом и его размером по умолчанию приблизительно соответствует длине символа, в котором измеряется ширина ячейки.
- Размер шрифта, чтобы рассчитать коэффициент, увеличивающий или уменьшающий ширину ячейки в зависимости от его (шрифта) размера.
- Количество столбцов в объединенной ячейке, чтобы вычислить ширину одного столбца.
Макрос VBA Excel для автоподбора ширины ячейки с учетом размера используемого шрифта:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub PodborShiriny() Dim myCell As Range, myLen As Integer, _ myCount As Integer, k As Single, n As Single myLen = Len(CStr(ActiveCell)) myCount = Selection.Columns.Count n = 11 k = ActiveCell.Font.Size / n For Each myCell In Selection myCell.ColumnWidth = myLen * k / myCount Next End Sub |
Переменные:
- myCell — отдельная ячейка в объединенной;
- myLen — длина текста в активной ячейке;
- myCount — количество столбцов, которое соответствует количеству горизонтально расположенных отдельных ячеек в объединенной;
- k — коэффициент, вносящий поправку в зависимости от размера шрифта;
- n — размер шрифта по умолчанию.*
* У меня по умолчанию установлен шрифт Calibri размером 11, поэтому n = 11. Если у вас по умолчанию Arial размером 10, тогда и в коде укажите n = 10. Далее подкорректируйте значение переменной n опытным путем, так как длина текста зависит от процентного соотношения широких и узких символов, если шрифт не моноширинный. Переменной n можно присваивать и дробные значения для более точного автоподбора.
Данный код VBA Excel работает с выделенной ячейкой. Вы можете задать список адресов объединенных ячеек и пройтись макросом по каждой из них.
Обработка списка ячеек
Укажите список объединенных ячеек в качестве аргументов функции Array. Для списка используйте адреса только первых ячеек из состава объединенных. Можно добавлять в список и адреса одиночных ячеек.
|
1 2 3 4 5 6 7 8 |
Sub ObkhodYacheyek1() Dim myCell() As Variant, myElem As Variant myCell = Array("A1", "D1", "G1") For Each myElem In myCell Range(myElem).Select Call PodborShiriny Next End Sub |
Переменные:
- myCell — массив со списком адресов объединенных ячеек;
- myElem — используется как элемент массива myCell.
Макрос ObkhodYacheyek по адресам из списка обращается к каждой ячейке по очереди, выделяет ее и запускает код автоподбора ширины PodborShiriny.
Если выделить диапазон объединенных и необъединенных ячеек по одной, удерживая клавишу 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 PodborShiriny Next End Sub |
Здравствуйте, Евгений. А про автоподбор высоты строки объединенных ячеек есть у вас статья? А то каждый раз высоту строки вручную корректирую при разных длинах текста.
Здравствуйте, Алексей!
Добавил информацию по автоподбору высоты объединенной ячейки.