Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Autofit All Columns In All Worksheets


I'm trying to not have to go through every worksheet in my large workbook to autofit the columns in each worksheet to my cell content.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Autofit All Cells In Many Worksheets
1. autofit all cells in 38 worksheets
2. copy the previous row without looking up or deleting or inserting. Just a simple copy previous row of values and formulas down one (to the next row empty or not)
3. Hide columns with headings hide in the first cell of the column.

Remember, I have 38 worsheets in the same workbook that it has to cycle thru. 4 of those are pivots, so no need to include those.


View Replies!   View Related
Autofit All Columns
I am using Excel 2007 and I would simply like to autofit every column o that the information fits and is visible. I would do this myself, but when you have to open many files with as many as 100 columns, its not fun.

View Replies!   View Related
AutoFit Visible Columns Only
I have a worksheet that has several hidden columns and wish to have the EntireRow.Autofit apply to all rows in the current region based on visible columns. After using the WrapText method on all visible cells, one of the hidden columns with a lot of characters causes the entire row to be much larger than desired. Is there a way to use EntireRow.Autofit to only expand the height of each row in the current region as necessary based only on the visible cell contents in that row?

Set AllCells = ActiveCell.CurrentRegion
ActiveCell.CurrentRegion.Select
With AllCells
.Font.Size = 6
.Font.Bold = False
.WrapText = True
.EntireRow.AutoFit = True
End With

View Replies!   View Related
Autofit All Columns Of The Activesheet
I am trying to write a small sub which will make do an "AutoFit" for all columns of the Active Sheet. For example, the code below does an "AutoFit" for columns A:J of a sheet. How can I make it do the same for all columns (not only for columns A:J) of the ActiveSheet?

Columns("A:J").EntireColumn.AutoFit

View Replies!   View Related
Autofit Columns (A:G) On The Last 5 Sheets Using VBA
I have an excel workbook with 7 worksheets. I am wanting to autofit columns (A:G) on the last 5 sheets using VBA.

View Replies!   View Related
Create New Workbooks From Filtered List Then Autofit Columns
I used the code below to successfully create workbooks from the filtered list using the below code provided on this forum (see link below). The only problem is that I want the resulting columns to fit to width.

Looping Through A Range - Use Result As Criteria In A Filter

View Replies!   View Related
Copy Columns Between Worksheets If The Columns Don't Match
I have 2 worksheets in which I have to copy one column of cells from one to
the other. The problem is that the "main" worksheet lists nearly 3,500
clients and I have to copy numbers into the "main" worksheet on weekly basis
from another worksheet that lists only about 1,100 of those 3,500 clients.

Both client lists are in alphabetical order, but I obviously cannot just copy
one column into the other, because then the numbers won't be matching the
correct clients.

View Replies!   View Related
Way To Autofit Whole Workbook?
Is there a way / code i can put into a macro which can autofit a group of sheets or a whole work book. I have a workbook with 100's of sheets and it takes alot of time to click each sheet and autofit them manually.

I do run a macro for each sheet, so if i could build it into a macro by adding the code to it that would be great

View Replies!   View Related
Autofit Selection Is Not Working
After Format > Column > Autofit Selection, the cell width is STILL not sufficient. Values with many digits do not show up in the print out (only ######s appear). What should I do?


View Replies!   View Related
Autofit, But Not The Complete Column
I have some Worksheets in which cell A1 contains a textual heading for each Worksheet.
On each Worksheet, cells in the range A2 to O5 containg headings; the data goes in rows 6 onwards.

In VBA script, I'd like to autofit columns A to O, but starting at row 2 so the text in A1 is not included. For each sheet, I can determine the last row, to get a "range" to autofit (e.g. "A2:P15").

I've run a search on Autofit; the only examples I can find autofit the whole column or set of columns.

Is it possible to autofit starting at a specific row?

View Replies!   View Related
Autofit Rows Using Macro
I'm trying to Autofit a number of Rows within a macro. I need to autofit Rows 8 to 80 as the content of these rows will have been updated by lines earlier in the macro. Some rows will now contain more than 1 line of text whereas others will now be blank.

I initially tried to use the

View Replies!   View Related
VBA To Autofit Row Height With A Minimum
I have a range named "rngOrders" on an Excel 2003 worksheet. I'm looking for a VBA macro that will set the row heights of the range to autofit (for rows/cells with wrapped text) and also set a minimum row height of 19.5 points for all rows.

View Replies!   View Related
Autofit On Workbook Open
autofitting on workbook opening. i'm using on workbook_open:

columns("b:b").entirecolumn.autofit

but underneath, i have a data query range that refreshes and best fits the columns accordingly...(over writing the autofit...)?

View Replies!   View Related
Calling Columns Into Different Worksheets
I'm not sure whether this should be a general question or it should go in the programming section... but here here goes anyways

I need to create a spreadsheet where the contents of Col A & B in Sheet1 are copied into both Col A & B in Sheet2 & Sheet3. Sheet1 Col A&B will be continuously updated (rows will be added) and as these columns are updated, Col A & B in Sheet2 & Sheet3 should be updated automatically and keep the format that is set in Col A & B in Sheet1.

Now, Col C&D in Sheet2 & 3 will be updated by a different user. As the user updates the cells in Col C&D in Sheet2 & 3, Col C&D in Sheet1 should automatically be updated with the data in the cells of Col C&D in Sheet2 (and keep the format) and Col E&F in Sheet1 should automatically be updated with the data in the cells of Col C&D in Sheet3 (and keep the format).

View Replies!   View Related
Combine Two Columns From Different Worksheets
I have two worksheets with a column of part numbers. I am trying to combine them in a new worksheet and remove duplicates.

View Replies!   View Related
Compare Columns Between Worksheets
I have two worksheets, PM1DATA and AMDATA, i'm looking for a way to compare the two sheets and have any data that appears in collumn B within the AMDATA sheet, but NOT within collumn B of the PM1DATA sheet to be copied and moved to a sheet called NEWKITS. I would like the entire row to be copied when new data is found, not just collumn B.

View Replies!   View Related
AutoFit Row Height On A Merged Cell
I'm using Office 2007 professional and I'm trying to format a cell (merged cell) so that when you enter text and it goes beyond the size of the designed cell that it will automatically continue and shift the additional text downward, like a paragraph. Also it shift everything below down with it. Is this possible? I have already tried wrap text field and AutoFit row height. This keeps it within the cell but it is not visible or printable once you tab out. I would like to have it continue like in a paragraph. Like a word doc may do. Itís visible and it shifts everything below downward as it grows, which also allows it to be printable.

View Replies!   View Related
Autofit And Sort On Workbook Close
I am trying to manipulate some worksheets during a Workbook_BeforeClose event. On sheet names that end with "sd" they need to alphabetize the data and autofit the columns. All sheets should hide on close, except "order". This is what I have but warning - it did something bad to my workbook. I can't get it back again. Had to force close Excel.

View Replies!   View Related
Autofit Merged Cells From Pdfs
I've just created a spreadsheet that we will be using as a project review form. In this spreadsheet I have multiple rows of merged cells, column D to N, for comments. I need these comment areas, merged rows, to autofit whatever is cut and pasted or typed into them.

The first problem is cutting and pasting from a pdf. All of the documents we received for the projects we bid are in pdf format. We often cut and paste from those documents into an older version of our review sheet that was built in Word. Trying to cut and paste the same information into Excel ends up in failure. Is there a way that this can be done without jumping through hoops?

The second problem Iím running into is when I cut and paste into a comment area I get the typical Excel error message that the information being pasted does not fit the area it is being pasted into. It there a workaround for this?

My third problem is autofitting the pasted information into the merged cells. Iíve seen some VBA and tried a couple but they donít work automatically when the comments area is filled. How can I do this? I donít know VBA. I'm using MS Office 2003

View Replies!   View Related
Lookup/Match/Sum - Several Worksheets And Different Columns
I'm working on budgets (salaries). I have one main spreadsheet with all employee names listed - other spreadsheets are for each department. I need to bring the total salary from the department spreadsheets and would like to do this by looking up (or matching?) the names in the column from the main sheet to the department sheets and then putting the salary from the department spreadsheet to the main spreadsheet. In addition, I have several that have their salary split between two or more departments. On my department spreadsheet I have a column labeled % of salary for this department. On the main spreadsheet, I want to figure out a formula that will sum the percentages for a certain person (so I make sure I don't go over 100%).

For example - Joe Smith's name is on the main employee spreadsheet. He is also listed on 3 other department spreadsheets, with % of salary listed as 30% for department 1, 30% for department 2, and 40% for department 3. On the main spreadsheet I need to add up Joe's percentages from the department spreadsheets.

View Replies!   View Related
Matching Two Columns In Two Separate Worksheets!
I have two worksheets with one row of 'accounting' type numbers in A1:A100 (it can be shorter or longer) on both sheets. I need to run a search on worksheet one to see which of them exactly match any on the second worksheet and then highlight them.

It can be a macro or it can highlight each entry a different color. Something so that I can determine which two numbers go together and I can run the thing only once.

There most likely will not be multiples on the same worksheet but if there are it will be few. If multiples happen it is ok it just needs to be matched to the second worksheet in a different color than the other similar numbers.

Another thing is if there is no match in either worksheet that is ok it can just skip to the next number in the list and check that one. There does not need to be a match.

View Replies!   View Related
Auto-Concatenate Columns-row Within Worksheets
How do I create a code that will concatenate the number in Column B, C and D into Column E for each row within each worksheet of the file, excluding the "Update" worksheet. If there is no number is each column of the row, then do not produce a record for that row in column E. Start executing function on row 2.

eg E2=b2&c2&d2=047, E3=b3&c3&d3=964, E4 cell is empty, because there is no record in b4, c4 and d4

View Replies!   View Related
Compare 2 Columns From Diff Worksheets..
I'd like to compare 2 columns from different worksheets in the same workbook with a twist.

If any and all data from worksheet 2-column c matches any and all data from worksheet 1-column c, then go back to worksheet 2-column B on the same data matching row, copy the data from that cell and paste it into the same row of the data match in worksheet 1-column b and paste it.

View Replies!   View Related
Delete Rows/columns Across All Worksheets
I have a single workbook with 55 worksheets. I need to delete rows #1-14, then delete columns A & B on all worksheets. The macro below seems to be running in a loop which appears to delete rows 1-14 and columns A & B until there is no more data left on the active sheet only. It does not run on any of the other sheets - even if I select all sheets before running the macro.

View Replies!   View Related
Compare 2 Columns On 2 Worksheets For Matches
I know there is a forumla to compare a small database to a list, but can't find it (I used to use French Excel).

Here is an example :

In a sheet, my small database :

(in two different cells)

Value1 and ValueA
Value2 and ValueB
Value3 and ValueC

etc...

In another sheet, my list :

I have a list where there's a lot of Value1, Value2... in one column, and in the next column, a lot of ValueA, ValueB....

I want to check in my list : if I have Value1, then, on the same row (next column) I have ValueA, if I have Value2, then, I should have in the same row (next column) ValueB....

And if I have Value1 and in the same row (next colum) ValueB, I want some kind of "Error" text.

Can't find this function nor (of course) the criteria to use it correctly.

View Replies!   View Related
Combine Columns Of All Worksheets & Add Worksheet Name
I have about 100 worksheets that I need to combine the data from column A into 1 worksheet and place the name of the worksheet the data is coming from in column B of the combined worksheet. example

worksheet 1 named New York contains the following in column A

11518

11563

11572

Worksheet 2 named California contains the following in column A

93510

90247..............

View Replies!   View Related
Compare Columns In 2 Worksheets And Copy From Same Row
I'm trying to do the following comparison and copy:

Step 1: See if value in Sheet1 column A matches any cell in Sheet2 column A

Step 2: If a match exists then copy value from that same row on Sheet2 column C (Min) to the same row on Sheet1 column D (Min)

Step 3: Do the same with the Max columns

For example, Sheet 1:A5 (11PL10012) matches Sheet2:A5 so copy Sheet2:C5 to Sheet1:D5 (the Min columns).

I've attached a sample spreadsheet for your review.

View Replies!   View Related
Listbox Column Widths And Lengths -Autofit
I've got a Listbox in a User form that looks at data on another spreadsheet purely for informational purposes, not to be selected. I have two problems with what it's doing:

1) There are 23 columns, of varying widths. However the listbox uses a standard width, so some items are truncated and some have too much room.

2) The data changes, and the number of rows may change from 2 to 15000. I've set the RowSource to cover the ranged from A1:W15000, but if only 2-300 lines are selected the user can't grab the scrollbar button to scroll through, but must instead use the arrow, which is rather slow.

Is there any way to set the column widths to match the data, and the Range to only go as far as the last populated row?

View Replies!   View Related
Me.Rows.Autofit With A Protected Sheet Not Working.
Me.Rows.Autofit with a Protected Sheet not working.
Basically I want to use vb code which is

View Replies!   View Related
Autofit Merged Cells With Wrap Text
I found referred to writing a vba code such as the one below:

Sub Format()
Rows(x).entirerow.autofit
End Sub

I have tried a vba code such as the one above but the issue persists.

I have a worksheet with merged cells [horizontal]; each cell is also formatted to warp text.

The issue is that the row size or cell size [vertical] does not adjust when the user types more text than what the cell size can handle.

View Replies!   View Related
VBA To Compare Worksheets; Update Certain Columns If A Match; Add New Row If Not
I would like a macro that can match column A & column B data of Worksheet(WeeklyJob) to column A & column B of Worksheet(Master) then if match is found copy column C through column F into Worksheet(Master) column C through F (and overwrite any [outdated] existing data there may be in those columns [thus updating the job's weekly charges, etc.]).
If match is not found I would like it to copy entire row from Worksheet(WeeklyJobs) into first blank row at end of Worksheet(Master) (thus giving me a new record of a new job from the weekly report).

All the columns in both worksheets are labeled the same (& row 1 is headings).


View Replies!   View Related
Conditional Copying Of Columns Between Worksheets/Workbooks
i want to copy data from one excel workbook to other (or ever between diff sheet wud do ) on basis of search on a complete column by picking data one by one from other column & on finding the exact match copy 4 columns form one workbook(or sheet) to other. this has to be done 4 a bulk of data (1000+) so manually is next to impossible.

View Replies!   View Related
Autofit Problems With A Row That Includes A Formula Referencing Another Cell
I have a document that I created that has merged cells. In order to autofit the rows of this document, I referenced the merged cells in singular cells that are not visible to the users of the document. (They are not hidden)

It had been working relatively nicely, but now the autofit function is not working correctly. If I copy and paste values into the cell it will work, but this would not be my ideal solution. I have tried playing with the number format, which works for the most part if I set it to "&" - text. It doesn't work for some longer cells with line breaks in them.

View Replies!   View Related
Expand/Collapse Columns On Password Protected Worksheets
Is there a way to password protect a document AND still allow for a user to expand or collapse columns?

Currently, it appear that when a worksheet is password protected, one cannot expand or collapse a column.

View Replies!   View Related
Compare 2 Columns On Different Worksheets Then Copy And Paste Cell Value
I'm alittle new to excel and MrExcel website. I have a spreadsheet that im trying to get a formula to work as below. If anyone knows how to get this to work please help! Thank you.

My spreasheet has the following:

Sheet1:
Column B Column G
1 test 1
2 center 2
3 school 4

Sheet2:
Column B Column G
1 work blank
2 school blank
3 home blank

I'm trying to find the value from sheet2 cell B2 (school) in sheet1 column B and then when it finds that, I need it to copy the data from the Corresponding sheet1 Column G cell to sheet2 Column G cell.

View Replies!   View Related
Inserting Entire Blank Columns In Pre-existing Worksheets
firstly i am cross-posting this topic so here is the URL to the same thread in a different forum.

[url]

whats up everybody? i have to use two excel worksheets that someone else already created to make CSV (comma seperated value) files. these CSV files are to be dumped into a database. the problem is that the table has more fields than the spreadsheet does (the spreadsheet has 3 fields - network, mask, size - and the table has 10 fields). i can insert one entire column before (to the left of) network (for the ID field in the table) without a problem, but when i try to insert entire blank columns after size (to the right of the pre-existing columns) it only makes columns for the first 14 rows. i know this because when i save it as a CSV file there are only 6 extra commas for the first 14 rows. is there a way to insert an entire blank column for the entire spreadsheet (as in all the way to the bottom)? i dont want to have to manually type in all those commas. i am using excel 2002.

View Replies!   View Related
Summing Values From Multiple Worksheets Based On Rows And Columns
I have Master sheet where I collect info from sub sheets. All sheets are similarly formatted, ie. product numbers on column A and headers on row 2. I need to sum values from all sheets based product number and header. Master sheet includes all product numbers and some extra headers, sub sheets include only needed numbers. Headers on sub sheets are identical.

Currently I have this thing solved with following formula:

N48=sumproduct(sumif(indirect("'"&$B$378:$B$385&"'!A:A");A48;indirect("'"&$B$378:$B$385&"'!L:L")))

Where B378:B385 includes sheet names.

But problem with this is that column is hard coded, so I have to know that that value I am looking for is in column L. That wouldn't be show stopping problem on its own, but I have columns all the way to DR and copying formulas for each column takes a lot of time when I have to manually update each column. Just copying cell holds that L:L and doesn't change it.

So, in addition of getting values for specific product number I need to get values from specific column based on column header.

View Replies!   View Related
Delete Row Of Duplicate Column Cells By Comparing 2 Columns Across 2 Worksheets
I am looking for a macro to look in Sheet 1 column A and compare the values to Sheet 2 column O. When it finds a duplicate I want it to delete the entire row in sheet 1. I dont want to have to manually sort anything if that's possible.

View Replies!   View Related
Track A Number Of Expense Items Accross 15 Worksheets With Up To 500 Rows Accross 30 + Columns Per Worksheet
I'm looking to use excel to track a number of expense items accross 15 worksheets with up to 500 rows accross 30 + columns per worksheet. Many of the learned people in this forum have helped me get this far, now I need some more assistance - please.

In my speadsheet I have a vlookup formula that returns a value from another worksheet. Here's an example.=IF(ISERROR(VLOOKUP($D3,Room_Configs!$A$1:$BO$3006,MATCH(M$1,Room_Configs!$1:$1,0),FALSE)),"",VLOOKUP($D3,Room_Configs!$A$1:$BO$3006,MATCH(M$1,Room_Configs!$1:$1,0),FALSE))

This works brilliantly. Now here comes the tricky part. What I'd like to do is append that formula with another one to do a vlookup on a second worksheet.

If both lookups return a value then I'd like the value of the 1st vlookup returned in the cell. If the value of the 1st vlookup is "0", then I'd like the value of the 2nd vlookup returned, and if the 1st and 2nd vlookup values are blank then a "0" is returned.

The name of the 2nd work sheet is "Non_Network_Equip"

Finally, it would be really great if the font colour for values returned from the 2nd vlookup forumla was blue.

View Replies!   View Related
Move Multiple Columns From Multiple Worksheets Into 1 Column
I have an excel workbook with 8 worksheets. Each worksheet has vertical columns (approx 250 columns per sheet) of numeric data. Is there a function or macro that will combine all of this data into one vertical column without having to individually cut and paste each one into the new column?

View Replies!   View Related
Consolidate Multiple Columns In Multiple Worksheets
I am trying to consolidate information from multiple (atleast two or more) worksheets (in the same workbook) into a single sheet.

The worksheets have identical column headings. There are 4 columns in each worksheet. The first three columns are text strings (and some cell in these columns may be blank). The fourth column is an integer. The number of rows in each worksheet is most likely to vary. Within a worksheet the rows are unique. However, some rows may be duplicated across worksheets.

To reiterate, here's what I am trying to do.

Step 1. Create a summary sheet.

Step 2. When there is a unique row (identified by the first three cells in that row) across worksheets then I have that row as is in my summary sheet.

Step 3. When there are duplicate rows (identified by the first three cells in that row) across worksheets, I want to add up the cell in the fourth column associated with those row in my summary sheet

I have tried union (works only within a sheet), consolidate (works only with a single column). I don't need the sophistication of a pivot table but simply an aggregation of data

I am trying to do this using macros (within an add-in)

I have enclosed a sample workbook with the expected output. I created this workbook by hand in an attempt to clarify my problem statement.

View Replies!   View Related
Autofit Row Height Plus Add "10"
Is it possible to autofit a row and then add "10" to the new height? Example: If autofit set the row height to "15" I would want to change the row height adding "10" (25 total). Autofit would have to be first.

View Replies!   View Related
Loop Through Worksheets Not Working (delete Some Hyperlinks In Column A On 50+ Worksheets)
Just need to delete some hyperlinks in column A on 50+ worksheets. Thought a loop through all the worksheets would do it. Only works on active sheet. Forgive my ignorance, don't really even know where it goes, once it works - module or workbook?

View Replies!   View Related
Worksheets, One With "supplier Names", "order Numbers" And "delay In Days" As Columns
I have two worksheets, one with "supplier names", "order numbers" and "delay in days" as columns. The other one with "supplier names" and "average delays" as columns.

It is the column "average delays" that I have issues with, I need Excel to search the "supplier names" column in the "delays" sheet and identify every specific supplier name, connect the delay in days for that order and calculate the average delay in the right cell of the column "average delays" in the sheet "delay statistics".

I have tried the help files and to search this forum but I have found nothing. I have also with my knowledge tried a few different ways using the IF function but nothing so far.

This is something I started doing but it is of course far away from any truth.

=IF(;Delays!B1:B200="Greber")..............

This is the "delays" sheet where I want to find my info. Nothing in the delay column stands for "no delay"...Kinda obvious but you know... So even the "nothing" needs to be included in the calculation..

View Replies!   View Related
Loop Across Worksheets: Perform The Same Process To All The Worksheets In My Workbook
I'm trying to perform the same process to all the worksheets in my workbook. This is the code I have now, but it will only apply to the single active worksheet:

View Replies!   View Related
Selecting Worksheets Array When #of Worksheets Will Vary
How do I modify this macro so that the worksheet array will select all the worksheets except sheet 1?? My workbooks will have varying numbers of worksheets ...

View Replies!   View Related
Clearing Data In All Worksheets Except Last 2 Worksheets
I need to write VBA code to clear all Values, all values beginning with an = sign for eg = 9725, except formulas and text on all my worksheets, except the last 2 worksheets.

View Replies!   View Related
Counting Formula (compare Values In 2 Separate Columns To See How Many Times The Same Value Appears In Both Columns)
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.

ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678

In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.

View Replies!   View Related
Subtracting 2 Columns Of Data From Each Other When Labels In Separate Columns Match
I have four columns of data, as follows:

label 1, value 1, label 2, value 2

I need to create a formula in the fith column that for each line will tell excel to:

look for entry in 'label 1' in 'label 2' if there is a match, then subtract value 1 from value 2, display result.

I have tried doing this with SUMIF but am getting nowhere fast....

View Replies!   View Related
Copy Columns Resulted From Another Columns Operations And Paste In New Sheet
when i copy columns resulted from another columns operations and paste in new sheet i got garbage ,could you tell me why and how to overcome this problem.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved