Converting Worksheet to all 'Upper'

Hi All,

I know how to change lower case columns to upper case with th
=upper(cell) function. Then once the new 'upper' column is complete
copy, then paste back as 'values' and then delete the old 'lower
column. 

Gosh, I have over a dozen columns to convert in each worksheet. Isn'
there a way to convert the whole worksheet to upper in one fell swoop?

While I'm here, I'd like to thank all those regulars here for the hel
they have contributed on a daily basis to all of us not s
knowledgable. 

You have saved many thousands of hours of frustration, and I, for one
am grateful! 

A Million THANKS FOR A JOB WELL DONE

--
Message posted from http://www.ExcelForum.com

0
7/30/2004 1:48:37 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
212 Views

Similar Articles

[PageSpeed] 40

AD, here is one way

Sub Upper_Case()
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
c.Value = UCase(c)
Next
Application.ScreenUpdating = True
End Sub


-- 
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
"ADArnold >" <<ADArnold.1a6oqz@excelforum-nospam.com> wrote in message
news:ADArnold.1a6oqz@excelforum-nospam.com...
> Hi All,
>
> I know how to change lower case columns to upper case with the
> =upper(cell) function. Then once the new 'upper' column is complete,
> copy, then paste back as 'values' and then delete the old 'lower'
> column.
>
> Gosh, I have over a dozen columns to convert in each worksheet. Isn't
> there a way to convert the whole worksheet to upper in one fell swoop?
>
> While I'm here, I'd like to thank all those regulars here for the help
> they have contributed on a daily basis to all of us not so
> knowledgable.
>
> You have saved many thousands of hours of frustration, and I, for one,
> am grateful!
>
> A Million THANKS FOR A JOB WELL DONE!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
newspab (272)
7/30/2004 1:55:38 AM
Paul B

Thanks for your quick response that worked great. One problem aros
that I should have expected:cells that had formulas in them ended up a
'Values'. 

If I had done a search before posting (instead of after posting) 
would have found this other elegant solution that works and doesn'
change formulas it runs across to Values:

Sub ConvertToUpperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = UCase(Rng.Value)
End If
Next Rng
End Sub

I found this solution at http://www.cpearson.com/excel/case.htm

In any case, thanks for taking the time to provide a macro that solve
the problem and I apologize for not doing a search before I posted

--
Message posted from http://www.ExcelForum.com

0
7/30/2004 2:45:32 AM
Hi Arnold,

If I were to run the example you found,  I would have to allow
about 3 minutes per column on my  600mHz machine.  Even on
a faster machine you would certainly notice the time consumed.

If conversion to uppercase were an Excel builtin conversion
you would certainly be selecting entire columns and not taking
extra pains to select a range that falls within your used cell
range -- the software or macro should be doing that for you.

It would be better to let the macro restrict the actual scope
within your selection so that you can select entire columns
and quickly perform your conversion, just like you would if this
were builtin  to Excel.

My  entire page concerns writing macros to run faster and to
help you recognize solutions designed to run faster.   Take a look at
   http://www.mvps.org/dmcritchie/excel/proper.htm#upper
which is part of web page
   Proper, and other Text changes -- Use of SpecialCells
   http://www.mvps.org/dmcritchie/excel/proper.htm
which goes beyond even just simply turning off screen updating
and calculation when a macro is running.

---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"ADArnold >" <<ADArnold.1a6rdu@excelforum-nospam.com> wrote in message news:ADArnold.1a6rdu@excelforum-nospam.com...
> Paul B
>
> Thanks for your quick response that worked great. One problem arose
> that I should have expected:cells that had formulas in them ended up as
> 'Values'.
>
> If I had done a search before posting (instead of after posting) I
> would have found this other elegant solution that works and doesn't
> change formulas it runs across to Values:
>
> Sub ConvertToUpperCase()
> Dim Rng As Range
> For Each Rng In Selection.Cells
> If Rng.HasFormula = False Then
> Rng.Value = UCase(Rng.Value)
> End If
> Next Rng
> End Sub
>
> I found this solution at http://www.cpearson.com/excel/case.htm
>
> In any case, thanks for taking the time to provide a macro that solved
> the problem and I apologize for not doing a search before I posted.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
dmcritchie (2586)
7/30/2004 8:52:25 PM
Reply:

Similar Artilces:

Convert Lead Notes, Activities and Security
As noted by others, the Activities and Notes do not convert when a Lead is Converted to an Account, Contact and/or Opportunity. It was recommended to simply click on the Originating Lead to get this information; however, this does not work if the Account has been re-assigned as the Lead ownership remains in tact and it is not Shared. Ex. We have a telemarketing team that works with the Leads. Once the Lead is nutured, it is converted to an Account, Contact and Opportunity. The Account is then Assigned to a Sales Person. The Re-Assignment then changes the ownership of the associated Con...

How to convert BMP file to JPG in VC 6?
Dear all, I'm using VC 6.0. Is there any API or class can convert a bmp file to a jpg formated file in MFC or ATL? Thx. Marco There is no API for converting BMP to JPG. You have to code that feature, but there is a free libs for that. Check www.codeproject.com "Marco Hung" <marco@xmlhk.com> wrote in message news:#Yy6Ju8ZDHA.3248@tk2msftngp13.phx.gbl... > Dear all, > > I'm using VC 6.0. Is there any API or class can convert a bmp file to a jpg > formated file in MFC or ATL? > > Thx. > Marco > > ...

Printing Page Numbers for Multiple Worksheets
I am trying to print an entire workbook and would like the page numbe to be specific to each worksheet set. For example, my workbook contains 7 sheets, each of which has a uniqu header that contains the command for Page X of X Pages. Each shee contains multiple pages. If I print the entire workbook, I can get the printout for each shee to begin with a 1 by manually setting the First Page setting to "1" i Page Setup. No problem there. However, my question is...Can I control the command for "X Pages"? Fo example, when I print the entire workbook, I'd like the first ...

Link Access table to Excel worksheet
I want to link these so that they can be edited in either programme. Whichever way, the link box is greyed out. You can make a linked table in Access. It's File - Get External Data - Link. The data lives in the Excel workbook, I think (I don't think Access keeps a copy of it). But you can change data in either Excel or Access. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "dalesrunner" <dalesrunner@discussions.microsoft.com> wrote in message news:A165D6C6-D173-45F7-94D5-352E3AA5121E@microsoft.com... ...

Can't move worksheet to new workbook
I have a client who has always imported comma delimited files into Excel and then copied that worksheet into a master .xls workbook using the Move function. With Excel 2007 this is no longer working. It gives an error stating that the destination does not have enough rows and columns. However this move function works if moving a worksheet that started off as an .xls file - not .txt. Could someone please offer some assistance? Thank you. Hi David Could you provide a few more details. You talk about comma delimited files at the beginning, then .txt files at the end. Are these files...

Convertion
Hi, I need to convert an excel 95 spreadsheet to excel 2003. got any tips, guidence etc. Bach -- bach ------------------------------------------------------------------------ bach's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26134 View this thread: http://www.excelforum.com/showthread.php?threadid=400694 "bach" <bach.1umcuf_1125475538.7378@excelforum-nospam.com> wrote in message news:bach.1umcuf_1125475538.7378@excelforum-nospam.com > Hi, > > I need to convert an excel 95 spreadsheet to excel 2003. got any > tips, guidence ...

HELP!! Charts won't copy across to another worksheet
Help Please. I have a sheet which has all my descriptive stats on it and graphs drawn from this data. There are 100 charts on the sheet. I am trying to copy the whole sheet WITH the CHARTS. Then I shall only have to change some of the formulas to perform other comparisons. My problem is whichever means I sue to copy the sheet the data cells copy. The gaps are there where the charts should be. NO CHARTS. Any advice grateful received. Have you tried making a second copy of the sheet by holding down CTRL and dragging the sheet tab to the right? best wishes -- Bernard V Liengme www.stfx...

help wanted in updating worksheet automatically
Could you please help us with an excel query. We have an excel file with fields in a random order. The values in these fields have to be picked up and arranged one below the other in a separate area. The values to be picked up always have a fixed cell address. Awaiting reply Elle How about just selecting the range, copy it, paste it to its new location. Then select that new range and do Data|Sort? Madhu wrote: > > Could you please help us with an excel query. > > We have an excel file with fields in a random order. > > The values in these fields have to be pic...

How do I open old Excel Worksheets when XP SP2 installed?
After installing XP SP2 (Service Pack 2), I can no longer open previously created Excel Worksheets. Newly created Excel Worksheets work fine. The error message for the olf Worksheets indicates that they are not Excel format. Should I go back to my "pre SP2" XP???? Mike I'm not going to be much help but want to get one thing straight. When you say XP SP2 do you mean Windows XP or Office XP(2002)? There is an SP3 for Office XP(2002) which you should download if you haven't done so. On Office download site. http://office.microsoft.com/en-us/officeupdate/default.aspx?disp...

Using Access Excel Automation, can I delete or rename a worksheet
Hi I am using automation to produce an excel workbook from an access database. Everything is working fine, except that my workbooks always have 3 additional worksheets - sheet 1, sheet 2 and sheet 3. Can I delete these, or rename them? Stapes On 13 Sep, 11:04, Stapes <steve.sta...@gmail.com> wrote: > Hi > > I am using automation to produce an excel workbook from an access > database. Everything is working fine, except that my workbooks always > have 3 additional worksheets - sheet 1, sheet 2 and sheet 3. Can I > delete these, or rename them? > > Stapes Found ...

Converting Leads to Opportunities in CRM 3.0 (changes since CRM 1.2?)
We are in the process of upgrading from CRM 1.2 to CRM 3.0. In 1.2 we were able to convert Leads to only Opportunities. However, in 3.0 it appears that you must also convert the Lead to an Account or Contact as well (i.e. after clicking on the Convert Lead button, if you select only the Opportunity checkbox, the OK button is disabled, however, if you also select either Account or Contact, the OK button becomes enabled). Is this a change in functionality from CRM 1.2 or is there a setting that impacts this behavior? Thanks in advance. ...

Worksheets in a workbook?
Hi all New around here and wondered if someone could tell me please how many worksheets I can get into an Excel 97 workbook. I could do with storing a lot of files and didn't want to link outside the workbook if I can help it - had too many bad experiences with data movement and corruption when linked to external sheets. Any help greatly appreciated Thanks Bob ..-.-. Worksheets by themselves, pretty much limited by memory only so if empty you can pretty much keep going. If you put stuff in them though, then that varies depending on what type of data you have in there and what sort of ...

printing worksheet on 3x5 card
I would like to know if it is possible to print a worksheet the size of a 3x5 card, so I can use it with my flashcards for school? Printerchallenged, if you printer supports this size paper then, file page setup, set paper size to 3x5, excel gets the paper size from your printer settings -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "printerchallenged" <printerchallenged@discussions.microsoft.com> wrote in message...

Limiting size of worksheet
I want to set up a worksheet and limit to 25 columns and 200 rows. I seem to can't find it. Any suggestions are appreciated. Jo -- JB ----------------------------------------------------------------------- JBL's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1504 View this thread: http://www.excelforum.com/showthread.php?threadid=26668 Joe You cannot do away with the unused cells. A worksheet has 256 columns and 65536 rows. You can hide the rows and columns you don't want to see. Select them and Format>Hide. Alternative.........You may want to...

How do I convert pounds to stones eg 200 pounds is 14 stone 4 lb
I have a table of weights, in pounds. How do I convert them to stones and pounds in excel. I dont want to say 14.5 stomes, I want to say 14 stones 7 pounds. Thanks Hi, With pounds in a1 try this =INT(A1/14) & " Stones " & MOD(A1,14)&" Pounds" Mike "J" wrote: > I have a table of weights, in pounds. How do I convert them to stones and > pounds in excel. I dont want to say 14.5 stomes, I want to say 14 stones 7 > pounds. > Thanks Try this: =TRUNC(A1/14) & " STONES " & ROUND(SUM(11/14,-TRUNC(11/...

Excel
Microsoft Office 2003 - Excel. Converter numeros em texto dentro de célula . Há uma função muito interessante que possibilita essa operação automaticamente, mediante formula. Só que o resultado é numa linguagem muito esquesita para mim ( não é em Português), aparece em THAI (creio ser em Thailandês). Como formatar para Português ? Forne=E7a por favor um exemplo de sua f=F3rmula, o resultado da f=F3rmula, e o que voc=EA deseja ver na f=F3rmula. Eu n=E3o sou certo se o formato da pilha estiver fornecendo a influ=EAncia tha...

Comparing data in two similar worksheets
I have to compare data in two reports to find the differences. The report has approx 15 columns and 2500 rows, and I need to extract each entry that has changed since the previous report. Each entry has an ID#, that may or may not have been in both reports, or is in both reports but has differing data in some of the columns. I am relatively new to using excel, so any help at all is appreciated. ...

Printing large worksheet onto HP Designjet 800 plotter
Trying to print excel spreadsheets with a very large number of columns (up to 400) onto an HP Designjet 800 Plotter using landscape layout. When using the "Fit to Page" or enlarging it over 180%, the columns on the end get cut off. We are using a customized paper size of 24" x 60" or 24" x 90". It cuts the worksheet off at the 60" length even if it is set to print to 90". We have tried updating drivers, increasing maximum font size to 1000 in the plotter properties. We were able to print the full spreadsheet by changing the fonts, but that ...

Opening worksheets takes ages
WinXP pro & OfficeXP pro Without any changes to both system and hardware Excel needs ages to open files. I can see 'requesting virus scan' in the status bar and from then loading even the smallest worksheets take around 20 seconds. Anyone on reason and workaround? Tnx Edwin Maybe you should take a look at your anti virus program instead.. -- Regards, Peo Sjoblom "Edwin Niemoller" <laagveen@planet.nl> wrote in message news:btmt4n$5ts$1@reader10.wxs.nl... > WinXP pro & OfficeXP pro > > Without any changes to both system and hardware Excel n...

Convert text numbers to number format
I have received a file that has a date column. However, the column is reading as text. I need to change the column so that the date reads as a number format. Any ideas? -- montagu ------------------------------------------------------------------------ montagu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15227 View this thread: http://www.excelforum.com/showthread.php?threadid=314927 Right click and reformat as you want then click on cell and press F2 an enter to refresh data that should do the tric -- scottymellot --------------------------------------...

Converting a Groupwise address book to Outlook 2002
Can it be done? Yes, it can be done. We just finished doing it. We used a product callled Wingra GroupWise Migrator for Windows. The cost is $10/mail box. We moved 135 mailboxes with calendar and contacts. www.wingra.com for more info. On Mon, 22 Dec 2003 13:08:15 -0800, "Will Straface" <anonymous@discussions.microsoft.com> wrote: >Can it be done? ...

Can't convert pictures
I just bought an 8mp digital camera, and Publisher 2000 can't convert them into a picture frame. Do later versions of Publisher accept these larger pictures? Thanks! (Would like an e-mail if you please. westley123@juno.com) Thanks again! Tom, Hard to tell from the scant info you provided, but are you downloading the pictures from your camera to your computer and saving them, then trying to import them? Just curious - what kind of a camera did you get? I didn't know there was an 8 mp camera available. What kind of zoom does it have? How many photos can it store and on how lar...

How can I convert an Excel file to an Adobe .pdf file by using Ex.
In the past, I have been able to convert an Excel spreadsheet directly to an Adobe .pdf file. I now have Excel 2003 and I cannot find the function to convert to .pdf anywhere. Am I missing something or is it no longer part of Excel? pammoore2000 wrote: > In the past, I have been able to convert an Excel spreadsheet directly to an > Adobe .pdf file. I now have Excel 2003 and I cannot find the function to > convert to .pdf anywhere. Am I missing something or is it no longer part of > Excel? It never was part of Excel. You must have had a third-party application or add-in...

VLOOKUP formula searching multiple worksheets ??
Does anyone know how to search for info from multiple Excel worksheets using the VLOOOKUP formula or any other formula. I have my VLOOKUP formula(column B) and list of values(column A) (sorted in ascending order) on Worksheet 7 and I need to search for these values in Worksheets 1,2,3,4,5& 6 Column F. Is this possible with Excel ? I've tried this by using the formula VLOOKUP(A2,Wrk1:Wrk6!$F:$G,1,FALSE) but I get a #VALUE! error. You will need a construct along these lines: =IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)), IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)), IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2...

How to remove DRM Protection from WMV/WMA/M4P/M4V/M4A/M4B/ASF and convert other popular video and audio even HD video 07-15-10
The guide shows How to remove DRM Protection from WMV/WMA/M4P/M4V/M4A/M4B/ASF and convert other popular video and audio even HD video Stpe 1: Download(http://www.5idownload.com/download/daniu-digital-media-converter-pro_full310_461741.exe) and install the Daniusoft Media Converter Pro( http://www.5idownload.com/dan-Media-Converter-pro.html ) Stpe 2: Run the software and click "Add…" button to load WMA or M4A, M4B, AAC, WMV, M4P, M4V, ASF files ( http://www.5idownload.com/img/guide-dan-media-converter-pro1.jpg) Step 3: Choose output formats. If you want to convert protected aud...