Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does this column have any "###"?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does this column have any "###"?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does this column have any "###"?
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 -- Rick (MVP - Excel) "Maury Markowitz" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does this column have any "###"?
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 -- "Maury Markowitz" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does this column have any "###"?
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 (MVP - Excel) "Rick Rothstein" wrote in message ... 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 -- Rick (MVP - Excel) "Maury Markowitz" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does this column have any "###"?
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. -- Rick (MVP - Excel) "PCLIVE" wrote in message ... 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 -- "Maury Markowitz" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does this column have any "###"?
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 -- "Rick Rothstein" wrote in message ... 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. -- Rick (MVP - Excel) "PCLIVE" wrote in message ... 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 -- "Maury Markowitz" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does this column have any "###"?
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does this column have any "###"?
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does this column have any "###"?
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). -- Rick (MVP - Excel) "Maury Markowitz" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does this column have any "###"?
On Jan 19, 3:50*pm, "Rick Rothstein"
wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does this column have any "###"?
Can you post a couple of examples that you tried which produce that error?
-- Rick (MVP - Excel) "Maury Markowitz" wrote in message ... On Jan 19, 3:50 pm, "Rick Rothstein" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chg 1 "Last, First Mid" column to 3 "First", "Middle", "Last" colu | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
Please add a "sheet" function like "row" and "column" functions | Excel Programming | |||
How do I split "A1B2" into "A1" and "B2" using text to column fun. | Excel Programming |