auto expand columns in vba

how can i add in my macro to auto expand all columns within my range "rg"?

thanks!!
eastsidemarketAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FamousMortimerCommented:
You mean autofit all columns?

This should work...

rg.Columns.EntireColumn.AutoFit


edit:
If it some of the columns don't seem to autofit, you can add this before the line above

rg.Columns.EntireColumn.ColumnWidth = 500
0
eastsidemarketAuthor Commented:
Hi FamousMortimer,
That is exactly what I mean. I tried the =500 line, that didnt work, got an error. I also tried the below and no error, but not autofitting. Any ideas?


Sub FilterFS(ws As Worksheet)
'start filter code
Dim rg As Range, rgCopy As Range
With ws
    Set rg = .Range("I1").CurrentRegion
    Set rgCopy = rg.Offset(1, 0).Resize(rg.Rows.Count - 1, rg.Columns.Count)
    iCol = .Range("I:I").Column - rg.Column + 1
    rg.AutoFilter
    rg.AutoFilter Field:=iCol, Criteria1:="FS"
    rgCopy.Copy
    With Worksheets.Add(After:=Worksheets(Worksheets.Count))       'Destination worksheet
        .Cells(1, 1).PasteSpecial xlPasteValues
        Cells(1, 1).Select
        .Name = "FS"
    rg.Columns.EntireColumn.AutoFit
    End With
    rg.AutoFilter
End With
End Sub

Open in new window

0
FamousMortimerCommented:
are you trying to autofit rg?  or the new worksheet (FS) that is being created?

my apology on the error.

rg.EntireColumn.ColumnWidth = 255
rg.Columns.EntireColumn.AutoFit

or if you want to autofit the new sheet, enter this within the with statement

    With .Cells.SpecialCells(xlCellTypeVisible)
        .EntireColumn.ColumnWidth = 255
        .EntireColumn.AutoFit
    End With
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

eastsidemarketAuthor Commented:
I want to do the entire sheet. My apologies for not explaining correctly.
however some cells are blank, so when i try what you mentioned below:

 With .Cells.SpecialCells(xlCellTypeVisible)
        .EntireColumn.ColumnWidth = 255
        .EntireColumn.AutoFit
    End With 

Open in new window


it does it for the most part, but then column K is really long and not showing the rest of the data. Maybe I should only focus on what I'm pasting into that sheet?
0
FamousMortimerCommented:
Is wrapping the text acceptable for column K?

.Columns("K").Cells.WrapText = True
0
eastsidemarketAuthor Commented:
still not working as it should.

i'd like it to just autofit after everything that has pasted.

Sub FilterFS(Ws As Worksheet)
'start filter code
Dim Rg As Range, rgCopy As Range
With Ws
    Set Rg = .Range("I1").CurrentRegion
    Set rgCopy = Rg.Offset(1, 0).Resize(Rg.Rows.Count - 1, Rg.Columns.Count)
    iCol = .Range("I:I").Column - Rg.Column + 1
    Rg.AutoFilter
    Rg.AutoFilter Field:=iCol, Criteria1:="FS"
    rgCopy.Copy
    With Worksheets.Add(After:=Worksheets(Worksheets.Count))       'Destination worksheet
        .Cells(1, 1).PasteSpecial xlPasteValues
        Cells(1, 1).Select
    .Name = "FS"
    With .Cells.SpecialCells(xlCellTypeVisible)
.EntireColumn.ColumnWidth = 255
.EntireColumn.AutoFit
.Columns("K").Cells.WrapText = True
Rg.AutoFilter
End With
End With
End With
End Sub

Open in new window


cant I just AutoFit everything that I copied and pasted into this sheet?
0
eastsidemarketAuthor Commented:
thanks for your help. this was resolved. have a few other threads if you don't mind helping ;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.