Does this column have any "###"?

[Maury Markowitz]

I have a large sheet that is created in VBA code running in Access. In
order to make the display look nice, when the numbers are finished
being pasted in I loop over the rows in the sheet looking for numbers
that are too large to fit and have been replaced by "####". It took me
a while to figure out how to do this (.text) but when I did I simply
used AutoFit and presto!

The problem is that the sheet is 50 columns wide and up to 3000 long.
Looping over the rows and looking in every column is eating up a
significant amount of time.

Is there some other way to find these things? Perhaps some way I can
examine a Region all at once? I see that .Find seems to work, but I
have hidden rows and .Find is unpredictable in these cases.

Maury

 

[Maury Markowitz]

Hmmm, I tried this...

Sub AutofitColumnIfRequired(aCol)
LastRow = ActiveSheet.Cells.Find(What:="*",
SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row
Set testRange = Range(ColumnNumberToLetter(aCol) & "1:" &
ColumnNumberToLetter(aCol) & LastRow)
Set foundIt = ActiveSheet.testRange.Find(What:="###",
LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext,
SearchOrder:=xlByRows)
If foundIt Is Nothing Then ActiveSheet.Column(aCol).AutoFit
end sub

The LastRow works fine, as does the testRange setting. But the .Find
gives me a 438. I tried removing various parameters until I was left
with just the What, but still the same error.

Any ideas?

Maury

 

[Rick Rothstein]

This will probably do what you want (just change the worksheet reference in
the For Each statement to your own worksheet's name)...

Sub AutoFitWhenNecessary()
Dim R As Range
Dim LastColumn As Long
Dim CurrentWidth As Double
On Error GoTo Whoops
Application.ScreenUpdating = False
For Each R In Worksheets("Sheet3").Columns
CurrentWidth = R.ColumnWidth
R.AutoFit
If R.ColumnWidth < CurrentWidth Then
R.ColumnWidth = CurrentWidth
End If
Next
Whoops:
Application.ScreenUpdating = True
End Sub

 

[PCLIVE]

Why not just select all the cells on the sheet by clicking the empty block
above "1" and to the left of "A".
Then double-click the line between the column headers "A" and "B".

HTH,
Regards,
Paul

 

[Rick Rothstein]

First off, my previously posted code erroneously Dim a LastColumn
variable... if you go with that routine, you can remove that declaration as
the LastColumn variable is not used in that code. However, using a
LastColumn approach to cut down on columns being processed, this code
appears to also work (and should be slightly more efficient than the first
code I posted)...

Sub AutoFitWhenNecessary()
Dim X As Long
Dim LastColumn As Long
Dim CurrentWidth As Double
With Worksheets("Sheet3")
LastColumn = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
On Error GoTo Whoops
Application.ScreenUpdating = False
For X = 1 To LastColumn
CurrentWidth = .Columns(X).ColumnWidth
.Columns(X).AutoFit
If .Columns(X).ColumnWidth < CurrentWidth Then
.Columns(X).ColumnWidth = CurrentWidth
End If
Next
End With
Whoops:
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

That will shrink column widths for columns containing short text or number
entries also. I assumed (in my responses) that the OP only wants to change
column widths for those columns where the entry doesn't fit while leaving
all other column widths as they are.

 

[PCLIVE]

That's a good point. I guess I assumed that, since the data was being
generated from access, it wouldn't matter if other rows were shrunk if their
contents were smaller than the default column width. In any case, I guess
it's not a bad thing for the OP know all these things.

Thanks.
Paul

--

 

[Maury Markowitz]

Ok, so I got this to work, but there's an oddity. The .Find only works
on an object that is a "Range(column-letter row-number : column-letter
row-number)". Any other format will give you an error. Once I learned
that, it started working fine. Odd! .Find does fail on hidden rows (or
columns), but in my case that is, in retrospect, exactly what I want!

Maury

 

[RadarEye]

Hi Maury,

If you are working with column-number in stead on column-letterts you
can use
Range(Cells(RowNumber, ColumnNumber),Cells(RowNumber, ColumnNumber))

HTH,

Wouter

 

[Rick Rothstein]

That is not really true... it works on any valid range (that you want to
search). For example...

MsgBox Columns("C").Find("Text To Find").Row

However, if what you are trying to find is not in the range you specify,
then an error is generated, so it is usually necessary to provide an On
Error trap to handle those cases. Your asterisk search, which could use the
Cells range (see my 2nd posted code elsewhere in this thread for an example)
would only need to have an On Error trap if you ran the macro against a
completely empty sheet (which I assumed you weren't doing in the
aforementioned code that I posted).

 

[Maury Markowitz]

That is not really true... it works on any valid range (that you want to
search). For example...

I tried a wide variety of formats, and most of them failed with an
"object does not support..." error. That is NOT the same as a "not
found" error.

Maury

 

[Rick Rothstein]

Can you post a couple of examples that you tried which produce that error?

--
Rick (MVP - Excel)

That is not really true... it works on any valid range (that you want to
search). For example...

I tried a wide variety of formats, and most of them failed with an
"object does not support..." error. That is NOT the same as a "not
found" error.

Maury