Как определить значения, которые не помещаются в ячейки Excel, с помощью VBA?

Мы создаем длинные листы Excel, используя различные инструменты, которые необходимо просматривать и использовать в качестве входных данных на более поздних этапах рабочего процесса. Проблема в том, что некоторые ячейки слишком малы для текста, который они содержат. Таким образом, люди и программы, читающие рабочие листы, не увидят одни и те же данные.

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

|Group|Def1 |Subgroup|Definition| Id   |Data |Comment   |
|-------------------------------------------------------|
| G1  |     | G1-1   |Important |G1-1-1|...  |          |
|     |Long |        |about G1-1|G1-1-2|.....|........  |
|     |text |-------------------------------------------|
|     |about| G1-2   |Another   |G1-2-1|...  |          |
|     |group|        |important |G1-2-2|...  |long comme|
|     |G1.  |        |text about|G1-2-3|     |          |
|-------------------------------------------------------|

Здесь некоторые ячейки в «Определении» и «Комментарии» видны не полностью. Есть ли способ найти такие ячейки программно?


excel vba column-width row-height
person jmster    schedule 07.06.2012    source источник
comment
Я смутно помню, как @SiddharthRout (возможно, это был кто-то другой) недавно опубликовал ответ на очень похожий вопрос. Надеюсь, этот человек опубликует свое решение здесь.   -  person JimmyPena    schedule 07.06.2012

Ответы (4)


arrow_upward
6
arrow_downward

Чтобы обнаружить эти ячейки (я не говорю об устранении проблемы), вы можете использовать метод Text объекта Range.

Например, Range("A1").Value может быть 123456789, но если оно отформатировано как число, а столбец недостаточно широк, Range("A1").Text будет "###" (или сколько знаков # помещается в ячейке).

person djjw    schedule 12.11.2012
comment
Я думаю, что Text просто возвращает значение, когда ячейка содержит строку. Он ничего не обнаруживает. Однако для дат и чисел это работает именно так, как вы описали, и это может быть полезно, спасибо. - person jmster; 19.12.2012

arrow_upward
5
arrow_downward

Вот трюк, который я использовал раньше:

With Columns("B:B")
    oldWidth = .ColumnWidth ' Save original width

    .EntireColumn.AutoFit
    fitWidth = .ColumnWidth ' Get width required to fit entire text

    .ColumnWidth = oldWidth ' Restore original width

    If oldWidth < fitWidth Then
        ' Text is too wide for column.
        ' Do stuff.
    End If
End With

Конечно, это будет применяться ко всему столбцу одновременно. Вы все еще можете использовать это, скопировав ячейки одну за другой в фиктивный столбец и выполнив там тест AutoFit.

Но, вероятно, более полезным для вас будет мой более ранний ответ на этот вопрос: Разделить текст на несколько строк в соответствии с шириной столбца. Он описывает метод определения ширины текста в любой заданной ячейке (и сравнения ее с фактической шириной ячейки, чтобы определить, подходит текст или нет).

ИЗМЕНИТЬ Ответ на ваш комментарий: если некоторые из ваших ячеек достаточно высоки, чтобы отображать 2 или более строк текста, вы можете использовать аналогичный подход, описанный в моем предыдущий ответ, сначала используя .EntireRow.AutoFit для определения высоты шрифта и .RowHeight для определения количества строк, помещающихся в ячейку, а затем выясняя, может ли текст поместиться в этом количество строк в ячейке такой ширины, используя метод предыдущего вопроса.

person Jean-François Corbett    schedule 08.06.2012
comment
Спасибо. Автоподбор имеет тенденцию разворачивать длинные строки и увеличивать ширину столбца, даже если для каждого значения достаточно места по вертикали. В моих примерах ваш метод дает ложные срабатывания почти в каждой ячейке. - person jmster; 08.06.2012
comment
Какие примеры?! Хотите поделиться ими с нами? - person Jean-François Corbett; 08.06.2012
comment
Примеры из реальной жизни огромны и содержат данные о клиентах, но я добавил к вопросу простую иллюстрацию. - person jmster; 08.06.2012

arrow_upward
1
arrow_downward

Разъедините все ячейки в книге и используйте Thisworkbook.sheets("Name").rows(index).entirerow.autofit И то же самое для столбцов. Какой смысл сохранять объединенные ячейки, кроме как по эстетическим соображениям? Учитывается только значение «базовой ячейки» (вверху слева).

person html_programmer    schedule 07.06.2012
comment
Спасибо. Полученные столы используются людьми, поэтому эстетические причины нельзя игнорировать. Тем не менее, избегать слияния, насколько это возможно, является очень хорошим предложением. - person jmster; 08.06.2012

arrow_upward
1
arrow_downward

Я столкнулся с той же проблемой сегодня. Я пробую этот трюк, чтобы увернуться от него. Возможно, это может быть полезно для вас. Он закодирован для работы с областями слияния шириной в одну колонку:

'Sheet2 is just merely support tool no data sheet in ThisWorkbook
With Sheet2.Range(target.Address)
    target.Copy
    .PasteSpecial xlPasteAll
    .UnMerge
    If .MergeArea.Count > 1 Then .UnMerge
    .ColumnWidth = target.ColumnWidth
    .Value = target.Value
    .EntireRow.AutoFit
    target.MergeArea.EntireRow.RowHeight = _
         1.05 * .RowHeight / target.MergeArea.Rows.Count
    .ClearContents
    .ClearFormats
End With

К сожалению, если есть несколько столбцов с такими объединенными ячейками, возможно, их взаимная необходимая высота будет конфликтовать друг с другом, и для восстановления гармонии потребуется дополнительный код. Выглядит как интересный фрагмент кода. Желаю вам найти это полезным.

person user6261023    schedule 27.04.2016