wasy macro question

I used the macro recorder to get the code to resize columns on a worksheet

Cells.Select
     Range("A1766").Activate
     Cells.EntireColumn.AutoFit

What I don't understand in the Range. It's seems to be referring to a 
single cell. Could someone explain to a VBA newbie?

I'm importing a text file and I want to resize the columns after import.
I have the import working just need this one last thing.

The worksheet will always have the same number of columns but the number 
of rows may vary will the above code still work?

gls858
0
gls858 (460)
3/12/2008 10:51:21 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
365 Views

Similar Articles

[PageSpeed] 37

gls858 wrote:
> I used the macro recorder to get the code to resize columns on a worksheet
> 
> Cells.Select
>     Range("A1766").Activate
>     Cells.EntireColumn.AutoFit
> 
> What I don't understand in the Range. It's seems to be referring to a 
> single cell. Could someone explain to a VBA newbie?
> 
> I'm importing a text file and I want to resize the columns after import.
> I have the import working just need this one last thing.
> 
> The worksheet will always have the same number of columns but the number 
> of rows may vary will the above code still work?
> 
> gls858

That supposed to be easy macro question

0
gls858 (460)
3/12/2008 10:54:21 PM
The range is a single cell, but you use entirecolumn later.

I would just use

Columns(1).Autofit

or

Columns("A").Autofit

-- 
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"gls858" <gls858@yahoo.com> wrote in message 
news:ePuiYOJhIHA.2084@TK2MSFTNGP02.phx.gbl...
>I used the macro recorder to get the code to resize columns on a worksheet
>
> Cells.Select
>     Range("A1766").Activate
>     Cells.EntireColumn.AutoFit
>
> What I don't understand in the Range. It's seems to be referring to a 
> single cell. Could someone explain to a VBA newbie?
>
> I'm importing a text file and I want to resize the columns after import.
> I have the import working just need this one last thing.
>
> The worksheet will always have the same number of columns but the number 
> of rows may vary will the above code still work?
>
> gls858 


0
bob.NGs1 (1661)
3/12/2008 11:02:16 PM
Bob Phillips wrote:
> The range is a single cell, but you use entirecolumn later.
> 
> I would just use
> 
> Columns(1).Autofit
> 
> or
> 
> Columns("A").Autofit
> 

Thanks Bob. I just posted what the recorder recorded. I'm a newbie when 
it comes to VBA. I'll give your suggestion a try.

gls858
0
gls858 (460)
3/12/2008 11:10:38 PM
This is what I get in XL97 when I use the Macro Recorder:

 Cells.Select
    Selection.Columns.AutoFit

You can of course take out the two Selects and just have:

Cells.Columns.AutoFit

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"gls858" <gls858@yahoo.com> wrote in message 
news:ePuiYOJhIHA.2084@TK2MSFTNGP02.phx.gbl...
>I used the macro recorder to get the code to resize columns on a worksheet
>
> Cells.Select
>     Range("A1766").Activate
>     Cells.EntireColumn.AutoFit
>
> What I don't understand in the Range. It's seems to be referring to a 
> single cell. Could someone explain to a VBA newbie?
>
> I'm importing a text file and I want to resize the columns after import.
> I have the import working just need this one last thing.
>
> The worksheet will always have the same number of columns but the number 
> of rows may vary will the above code still work?
>
> gls858
> 


0
sandymann2 (1054)
3/12/2008 11:14:34 PM
That is the problem with the MR, it records every step. So you select a 
cell, then you autofit the column, two steps, so that is what the MR 
records. A bit of a pain, but understandable.

-- 
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"gls858" <gls858@yahoo.com> wrote in message 
news:OffCKZJhIHA.4436@TK2MSFTNGP02.phx.gbl...
> Bob Phillips wrote:
>> The range is a single cell, but you use entirecolumn later.
>>
>> I would just use
>>
>> Columns(1).Autofit
>>
>> or
>>
>> Columns("A").Autofit
>>
>
> Thanks Bob. I just posted what the recorder recorded. I'm a newbie when it 
> comes to VBA. I'll give your suggestion a try.
>
> gls858 


0
bob.NGs1 (1661)
3/13/2008 8:23:22 AM
Sandy Mann wrote:
> This is what I get in XL97 when I use the Macro Recorder:
> 
>  Cells.Select
>     Selection.Columns.AutoFit
> 
> You can of course take out the two Selects and just have:
> 
> Cells.Columns.AutoFit
> 

Thanks for the input Sandy.

gls858
0
gls858 (460)
3/13/2008 6:19:33 PM
Reply:

Similar Artilces:

Access 97 Runtime question
We are using Access 97 runtime for some of our staff. I noticed today that in reports if they right mouse click, they don't get a shortcut menu. I want them to be able to Export the report they are viewing. Would Access 2003 runtime have the shortcut menu? Thanks Kelvin "Kelvin Beaton" <kelvin dot beaton at mccsa dot c o m> wrote in message news:uiQA0i7zHHA.1168@TK2MSFTNGP02.phx.gbl... > We are using Access 97 runtime for some of our staff. > I noticed today that in reports if they right mouse click, they don't get > a shortcut menu. I want them to be...

Optimiztion question
I was looking at MSDN on optimizing queries (http://msdn.microsoft.com/en-us/library/ms998577.aspx) and am unclear as to why a foreign key relationship affects the query in this case. At the beginning of the article it gives an example and starts out by saying: The sample screen shot in Figure 14.2 shows an execution plan that accesses only one table, although two tables are included in a join in the query. Because there is a declared foreign key relationship between the authors table and the titleauthor table, and the au_id column in the titleauthor table is not allowed to b...

spell check does not visually cycle through cells when run from macro?
I'm trying to recreate the same behavior in a macro that spellcheck has when the spellcheck button is pressed. The spellcheck does not cycle through the cells visually when run from a macro, so when there is a misspelled word, you cannot see the cell it is a part of. If anyone knows how I can get this behavior to work, it would be GREATLY appreciated. I did a record new macro, and got code similar to the following: Sub testSpelling() Range("F2:F500").Select Selection.CheckSpelling SpellLang:=1033 End Sub thanks, dan Dan, Add the fiollowing to your code as shown: ...

Setting directory for file save in macro
Hi Thanks to a generous poster I now have a great bit of code to use in a macro for Excel which saves a text file (I'm no VBA programmer so this was really helpful), i.e.: Open "Test.txt" For Output As #1 (rest of code) My problem now is this: The macro saves test.txt to Excel's current active folder, rather than the folder that the current workbook is contained in. For example, if I last saved an Excel workbook to c:\workbooks\, and the workbook that I have open exists in c:\workbooks\workbook1\, when I run the macro it saves the text file in c:\workbooks\. If I ...

Can you record a macro in Publisher like you can in Excel?
In Excel I use the feature to Record a Macro. Excel records the keystrokes and converts to VB. Does Publisher have that option? In my version, Publisher 2003, I can Create a Macro, and it takes me into VB. However, I don't know how to code VB. Darlene wrote: > In Excel I use the feature to Record a Macro. Excel records the keystrokes > and converts to VB. Does Publisher have that option? In my version, > Publisher 2003, I can Create a Macro, and it takes me into VB. However, I > don't know how to code VB. Publisher does not have a Macro Recorder, sorry. --...

Macros/Signatures Question
I have an Excel Workbook that I created for a PC Order Form. I set it up to only make certain rows visible based on the Machine Model that they choose. I digital signed the Worksheets and exported the Digital Signature Certificate and installed it on the User’s PCs. The code works great. The user opens the Workbook and it runs the Macros with no problem. The problem I have is when the user goes to save the Workbook. They get an error that “Excel can not sign VBA macros when saving to this file format. Do you want to remove the digital signature and continue saving this workbook?”...

Macro Copy and Paste
I am currently trying to copy from one workbook that has 7 differen tabs and paste into one worksheet on a separate master workbook. M problem is that when I set up the macro to copy from one of the tabs i the workbook and paste to the other work book, I only want the the row to come in where there is data. Currently I am having to run a macr that takes every 100 lines, but ideally I only want the informatio copy and pasted where there is only data. In addition, once I have copy and pasted everything from the first tab my next step is to copy and paste information from the second tab t th...

Referring to a FileName in Macro
I have a Macro with the following code Application.Run "TestQry!Macro2" Application.Run "TestQry!Macro1" TestQry is the name of the file. Is there anyway that this macro cod can refer to the activeWorkbook or filename so that if the file i saved to a new name this macro will still ru -- Message posted from http://www.ExcelForum.com How about just: macro2 or even call macro2 "DoctorV <" wrote: > > I have a Macro with the following code > > Application.Run "TestQry!Macro2" > Application.Run "TestQry!Macro1" > &g...

Macro Question #10
Hi, I am working with a text file that I imported into Excel. It is a transactional report of sales by customer. The data will have the customer number and name spread out over three cells. What I want to do is to create a macro that will contatenate all three cell and then perform a paste special and paste them into another cell in another column. Since the number of transactions by customer can vary the customer name and number can appear anywhere on the page. I created a macro to do this using the first customer which appears in cell B5 through D5. The next customer appears...

Simple IMAP question
Hi, How do I enable shared calendar with free/busy information through IMAP ? When I add the "calendar" folder in IMAP it displays as a mail folder, not a calendar. Pierrot Is this calendar a mailbox or public folder? Why are you using IMAP instead of MAPI? MAPI is much more feature complete as it applies to this sort of thing. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Pierrot Robert" <mcthepro@nospam.hotmail.com> wrote in message news:%23NzXwrV6GHA.4352@TK2MSFTNGP05.phx.gbl... > Hi, > > How do I...

Exchange 2003 Question??
Dear all, I would like to know if there is an upgrade path from Exchange 2003 Standard Edition with SA to Exchange 2003 Enterprise Edition with SA? Thank you. Regards, Kueh. This might help http://support.microsoft.com/default.aspx?scid=kb;en-us;170280 -- Santhosh Sivarajan MCSE(W2K/NT4),MCSA(W2K3/W2K/MSG),CCNA,Network+ "KA Kueh" <kka@ksm.com.my> wrote in message news:exVKwgOKEHA.1040@TK2MSFTNGP10.phx.gbl... > Dear all, > > I would like to know if there is an upgrade path from Exchange 2003 Standard > Edition with SA to Exchange 2003 Enterprise Edition wit...

question about leaving messages on server
My husband and I have 1 POP mail account. I am accessing it from a computer in Virginia, where I'm staying with relatives. He is accessing the same account from California. I have my Outlook set up to keep messages on the server, but to delete them after 1 day (so that he will still get the messages, but the server won't get clogged). His email program, Eudora, is set up so the messages will be deleted from the server after he downloads them. Is there a chance I won't get some messages because he has his computer set up that way? (He downloads it 1st, then it gets deleted off serv...

Questions on migration from VS2005 to VS2010
Hi folks, As is the case every couple of years, I go through a nerve-wracking transition to a new development environment. The thing is, I've become really comfortable with VS2005, and our small team has a very simple and effective approach to development. I am nervous that VS2010 will negatively effect our productivity; but don't want to get too "far behind" toolset versions. So, that being said, I wanted to ask a few questions on what to expect when moving from VS2005 to VS2010 on an upcoming project. Your feedback is highly appreciated. Question...

Spreadsheet macro stopped working!
Hi, I recently made an Excel spreadsheet for keeping track of my golf score. The spreadsheet is quite simple. It consists of 4 sheets: Scorecard, statistics, database and equations (for calculating some specific). I insert informations about the golf course I'm playing and how my score was etc. I then hit a button "Save scorecard" that runs a macro. The macro inserts specifik values from the scorecard into the database sheet (which works like a charm). The macro also updates the "Statistics" sheet with information about stroke number and points for ...

Filtering question
Is there a way to expand the filter option so that it shows 1 row (or more), both above and below any row that contains the value I am looking for? Data|filter|autofilter examines each cell/row on its own. Maybe you can put the key value for each group on the rows you want. Then filter by that column. richzip wrote: > > Is there a way to expand the filter option so that it shows 1 row (or more), > both above and below any row that contains the value I am looking for? -- Dave Peterson ...

New to Money 2006
I was using Money 2004 and upgraded it to 2006 last night (God only knows, why :)). I started to hate the idea of Passport and Web Publishing my account details and so did not choose the Passport option. I was able to install with regular Money stand alone password and still update all my accounts by downloading them from the respective financial institutions (as I used to do with 2004). The problem is with MBNA America. I want to download the credit card transactions. But the online setup process is asking me for passport setup. I looked at the description of this bank on Microsoft site...

Excel 2007 macro compatability
I have created a macro in Excel 2007 that includes importing a text file. It works fine on my PC, but fails on a co-workers PC running Excel 2000. The failure points to the "TextFile Platform" command in the macro. I have tried several (437, 1252, 20127), but get the same results. Any solution? Thanks you. Have you try xlWindows -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Al @ Frontier" <Al @ Frontier@discussions.microsoft.com> wrote in message news:3F601F80-F099-42C8-AA18-6D31A85946F9@microsoft.com... >I have cr...

upload form problem / server question
www.icingpictures.co.uk / win xp / frontpage 2003 Want to create a form with upload file form field - i read on this forum that the server type i use is important - it should be a windows type server with frontpage extensions - i currently use a linux server with front page extensions - do i need to change to a windows server? Thanks for taking a looka t my question. Joyce Yes, you must have a Windows Server, just like you stated you read. -- Tom [Pepper] Willett Microsoft MVP - FrontPage Since 1997 --------------------------- "threademporium" <threademporiu...

Outlook 2003 question #4
Hi ... I have 4 email accounts with outlook 2003 and they are all from the same isp provider ... My problem is when i am going to create an email to someone i can't tell which email account is sending the email to , its usually set a default ... What i mean is that when i view my email accounts they are listed as so and so default , # 1 , # 2 , # 3 , I don't know what 1 or 2 or 3 are ... Is there a way to notice which email account is which beside having numbers beside them ... I can't tell which is which ... It would be better if you could view the actual name of the ema...

Excel E-mail Macros
I need to know how to create a macro that will send an excel file as an attachment to one individual, and then send one of the file's worksheets to 3 e-mail addresses using the mail recipient method so that the worksheet appears in the body of the e-mail. The e-mail program I am using is Outlook. Any help with this will be GREATLY appreciatd. Hi John, I'd tell you, but Ron de Bruin already did all the work. It'd be silly for me to do it again...Try Ron's site: http://www.rondebruin.nl/sendmail.htm tim "John" <jbegley@dcas.nyc.gov> wrote in message ne...

format macro
Last week, Art was trying to help me with this, but the solution doesn't work for me. I'm looking for a macro that would be contained within my personal.xls, that I could use as needed. Lets say I have A1:C5 selected, which represent headdings and data for jan, feb and march. [On a different spreadsheet, it may be different columns, or a different number of columns. The point is that I will select the range before I run the macro.] I want to do an auto fit for each column, so that the column widths are big enough to fit the numbers. No problem, format-->column-->autof...

macro problems!
i have a check box whose control source is: =[Total In Stock]-[reorderlevel]<=0 how can i set the control to only be visible if this check box is true? tried macro and vba, but can't quite work it out. thanks in advance Paul ...

Macro #17
Does anyone have a Macro to edit a cell to add "=" sign at the beginning of the data Thanks, Alexandra can you be more pacific ActiveCell.FormulaR1C1 = "=" Range(" ").Select Alexandra wrote: > > Does anyone have a Macro to edit a cell to add "=" sign at the beginning of the data? > > Thanks, Alexandra ...

Tax Collected Question
Is there a report to run that will show the tax that has been paid by the customer and not on the tax that was collected via a tender? Or is there a report to run that shows invoices have been paid by check, credit card, or cash that leaves out those tendered to account? We would like to be able to pay taxes on what has been paid to us and not what we are waiting on for payment. hi send me your email I will forward you some interesting reports for your below issue. "bevans22" wrote: > Is there a report to run that will show the tax that has been paid by the > custome...

shorter macro
is there a way to shorten this macro to make it run faster thanks JD Range("C:C,D:D,E:E,F:F,G:G,H:H,I:I").Select Range("I1").Activate ActiveWindow.SmallScroll ToRight:=6 Range("C:C,D:D,E:E,F:F,G:G,H:H,I:I,J:P").Select Range("J1").Activate ActiveWindow.SmallScroll ToRight:=4 Range("C:C,D:D,E:E,F:F,G:G,H:H,I:I,J:P,Q:Q,R:R,S:S,T:T,U:U").Select Range("U1").Activate ActiveWindow.SmallScroll ToRight:=7 Range("C:C,D:D,E:E,F:F,G:G,H:H,I:I,J:P,Q:Q,R:R,S:S,T:T,U:U,V:V,W:W,X:X").Select Range(&q...