Identify when Excel is displaying ### in a cell? (contentswidth)
 

I am pulling data from an automated report. I'm trying to test for every
possible condition, so that my VBA will continue to work for the rest of the
year without additional tweaking.

The target report is produced from an aspx web page (which has an "export to
Excel" option). I do not have control over the source format.

Some data in the exported Excel report is in merged cells. When numbers are
increased (for example, to simulate the growth of YTD numbers) the display
converts to "######" when the digits exceed the cell width for that font
size. When I try to autosize columns by selecting the columns and
double-clicking between the column letters (cursor looks like <-|- ) which
normally autosizes the columns, nothing happens. I haven't tried to
investigate the root cause; right now I'm assuming it is because of the
merged cells.

I need to make sure that the cell contents are visible, because my code
takes ranges of cells and copies them as a picture to paste into a MS Word
template, at various bookmark locations.

Is there a way in code to either force the autosize in a way that actually
works, or to simply determine whether there are any values being displayed as
"###" so that I can loop and incrementally increase cell width until the
actual value is visible?

I hope that the source aspx page is smart enough to adjust the exported
column widths to adjust for the cell contents, but I'd rather program
conservatively and make sure my VBA handles all possible conditions (at least
the ones I can think of).

Many thanks,
Keith

Identify when Excel is displaying ### in a cell? (contentswidth)
 

Select a cell and run:

Sub PoundTester()
Dim s1 As String, s2 As String
s1 = ActiveCell.Text
If Len(s1) = 0 Then Exit Sub
s2 = Replace(s1, "#", "")
If Len(s2) = 0 Then
MsgBox " activecell is displaying pounds"
End If
End Sub

--
Gary''s Student - gsnu201002


"ker_01" wrote:

I am pulling data from an automated report. I'm trying to test for every
possible condition, so that my VBA will continue to work for the rest of the
year without additional tweaking.

The target report is produced from an aspx web page (which has an "export to
Excel" option). I do not have control over the source format.

Some data in the exported Excel report is in merged cells. When numbers are
increased (for example, to simulate the growth of YTD numbers) the display
converts to "######" when the digits exceed the cell width for that font
size. When I try to autosize columns by selecting the columns and
double-clicking between the column letters (cursor looks like <-|- ) which
normally autosizes the columns, nothing happens. I haven't tried to
investigate the root cause; right now I'm assuming it is because of the
merged cells.

I need to make sure that the cell contents are visible, because my code
takes ranges of cells and copies them as a picture to paste into a MS Word
template, at various bookmark locations.

Is there a way in code to either force the autosize in a way that actually
works, or to simply determine whether there are any values being displayed as
"###" so that I can loop and incrementally increase cell width until the
actual value is visible?

I hope that the source aspx page is smart enough to adjust the exported
column widths to adjust for the cell contents, but I'd rather program
conservatively and make sure my VBA handles all possible conditions (at least
the ones I can think of).

Many thanks,
Keith