Autofitting Columns

[SQChevy]

I've adjusted how I export a sheet. I'll attach the code below. I'm trying to then get the area to autofit the columns, but it doesn't seem to be working. Please let me know if you can see something wrong in the coding.

Please Login or Register  to view this content.

[Cheeky Charlie]

Please Login or Register  to view this content.

'

You don't want a '

get rid of the '

then it will work

HTH

[dominicb]

Good afternoon SQChevy

I'm trying to then get the area to autofit the columns, but it doesn't seem to be working. Please let me know if you can see something wrong in the coding.

Next time you post code please remember to wrap it using the code tags ("#" button in the toolbar above the editor window) - I've done it for you this time.

You seem to have commented out the "Autfit" line - does it work if this line is uncommented?

HTH

DominicB

[Cheeky Charlie]

You seem to have commented out the "Autfit" line - does it work if this line is uncommented?

No points for creativeness of answer

Creativeness is a word

[SQChevy]

THank you I was trying to figure out how to do that.

I sorry even with the code not a comment it wasn't working. I was moving things around to see if it was the location in the code affecting it. It wasn't working with the ' removed.

[Cheeky Charlie]

Fair enough, what do you mean by not work? Doesn't shrink enough? Doesn't grow enough?

If it doesn't move at all, have you tried, whilst stepping through the code, resizing the columns manually just before the autofit to see what happens then?

Are you sure there are not hidden strings somewhere whcih make the columns not a size you'd like.

[SQChevy]

It doesn't seem to move at all. It should expand to allow for the longer text. I will try moving them before the autofit code. (It didn't work) I got the coding for doing this step by recording the macro so it astounds me that it doesn't work. Do you think it has something to do with the screen updating? There shouldn't be any columns like that. There are a few hidden rows, but they shouldn't cause the columns to not autofit. If I then go it and double click the column boundary it autofits properly.

[Cheeky Charlie]

one idea:
Have a look at the text wrap property of the cells.
(Ctrl + 1) on the cells you'd expect to be good to you. Text wrap enabled could cause this...

re: screenupdating - no, I wouldn't have thought that could make the difference

You could also try defining the sheet where the columns are explicitly. i.e.:

Please Login or Register  to view this content.

This is generally better practice, most of your select and activate statements are unnecessary.

HTH

[Cheeky Charlie]

Maybe post an attachment if you're still stuck?

[SQChevy]

Cheeky...Thank you. Specifying the sheet is what did it. I had tried to do that by adding the activate on sheet 1, but I guess it didn't do what I was expecting.

[SQChevy]

One more question: is there a way to automate the deleting of the other sheets. It still asks if you want to delete. Since I'm moving to an empty worksheet, I just want the single sheet. We will usually be moving the sheet to another spreadsheet.

Any suggestions on how to add to an existing workbook? If it's open or not. Or is that another complicated beast all of it's own.