2 VBA Issues w/column size and return to A1

[jae113]

Hi All,

I'm pretty new at VBA, I've been using Macro Recorder in 2010 and working from there, so I apologize if any of this is a silly question.

Without having to re-record entire process, is there a way to write in an absolute return to A1 from any where in the workbook? I used releative references and a few little macros (that I would like to combine eventually) but I didn't record the return to A1. I tried Range("A1") .Select but got a syntac error

I have a two part issue with the column size and width. When the data comes in from Access, some is wrapped and the columnn width is wacky. I would like to make all the columns "fit to size" and manually to fix the wrap issue, is I select the entire worksheet and then wrap and unwrap it. Here is the code I used, but it doesn't seem to do anything:

Sub Wrap_Unwrap_Text()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
'' Wrap_Unwrap_Text Macro<o></o>
''<o></o>
ActiveCell.Cells.Select<o></o>
With Selection<o></o>
.VerticalAlignment = xlBottom<o></o>
.WrapText = True<o></o>
.Orientation = 0<o></o>
.AddIndent = False<o></o>
.IndentLevel = 0<o></o>
.ShrinkToFit = False<o></o>
.ReadingOrder = xlContext<o></o>
.MergeCells = False<o></o>
End With<o></o>
With Selection<o></o>
.VerticalAlignment = xlBottom<o></o>
.WrapText = False<o></o>
.Orientation = 0<o></o>
.AddIndent = False<o></o>
.IndentLevel = 0<o></o>
.ShrinkToFit = False<o></o>
.ReadingOrder = xlContext<o></o>
.MergeCells = False<o></o>
End With<o></o>
End Sub<o></o>


Any and all help very much appreciated! Thanks!

 

[jae113]

Should I split this into 2 questions?

 

[njimack]

You don't need to select cells to perform actions on them, which means you don't need to return to A1. Something like this...

Sub Wrap_Unwrap_Text()
With Cells
    .WrapText = True
    .WrapText = False
    .EntireColumn.AutoFit
End With
End Sub

If you want to ensure A1 is selected after the code has run, your original line of Range("A1").Select should have worked.

 

[jae113]

Thanks for getting back to me! The wrap and unwrap works great now. I'm having a lot of odd problems with the macro that I think are related to relative references, but I'm working on it.

For instance, I finally got almost all of my custom footer to work, with the exception that I can't seem to keep &n[] to stay on. It gives me the &p[], but always drops the total pages.


Thanks again