eastsidemarket
asked on
auto expand columns in vba
how can i add in my macro to auto expand all columns within my range "rg"?
thanks!!
thanks!!
ASKER
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?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
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?
however some cells are blank, so when i try what you mentioned below:
With .Cells.SpecialCells(xlCellTypeVisible)
.EntireColumn.ColumnWidth = 255
.EntireColumn.AutoFit
End With
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?
Is wrapping the text acceptable for column K?
.Columns("K").Cells.WrapTe xt = True
.Columns("K").Cells.WrapTe
ASKER
still not working as it should.
i'd like it to just autofit after everything that has pasted.
cant I just AutoFit everything that I copied and pasted into this sheet?
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
cant I just AutoFit everything that I copied and pasted into this sheet?
ASKER
thanks for your help. this was resolved. have a few other threads if you don't mind helping ;)
This should work...
rg.Columns.EntireColumn.Au
edit:
If it some of the columns don't seem to autofit, you can add this before the line above
rg.Columns.EntireColumn.Co