re-size columns

J

Jing

Active Member
Joined
Feb 11, 2011
Messages
289
So i have writen the following Code to perform a search for the criteria i specify but i am at a loss when it comes to organizing the data on the new sheet...

Code: 
Option Explicit
Sub formshow()
'Show Search Form
UserForm1.Show
End Sub
Function FilterAndCopy(rng As Range, Choice As String, Field As String)
 
    Dim FiltRng As Range
    Worksheets(Choice).Cells.ClearContents
    rng.AutoFilter Field:=Field, Criteria1:=Choice
    On Error Resume Next
    Set FiltRng = rng.SpecialCells(xlCellTypeVisible).EntireRow
    On Error GoTo 0
    FiltRng.Copy Worksheets(Choice).Range("A1")
    Set FiltRng = Nothing
 
End Function
Function CreateSheet(Choice As String)
Dim NewSheet As Worksheet
On Error GoTo Err:
Worksheets(Choice).Select
Exit Function
Err:
Set NewSheet = Worksheets.Add
    On Error Resume Next
    NewSheet.Name = Choice
    On Error GoTo 0
End Function

Here is what it looks like now.
<TABLE style="WIDTH: 1536pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=2048 border=0><COLGROUP><COL style="WIDTH: 48pt" span=32 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl78 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #273359" width=64 height=20>Day</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>Time</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>Start City</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>End City</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>Start Km</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>Finish Km</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>Total Km</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>Total Miles</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>Fuel Liters</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>Fuel US Gal</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>Liters</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>Imp Gal</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>Mpg</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>$ Per/L</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>$ Per/G</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>Total</TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64></TD><TD class=xl79 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #273359" width=64>Total</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>10th</TD><TD class=xl86 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8:45 PM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">City 1</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">City 2</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.00</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl77 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">325.00</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">325.00</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">201.99</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl75 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">30.145</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl74 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">30.15</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">6.63</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">30.46</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">$ 1.239 </TD><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">$ 37.35 </TD><TD class=xl81 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>11th</TD><TD class=xl86 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">11:14 AM</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl82 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">City 1</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">City 2</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl73 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0.00</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl77 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">315.00</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">315.00</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">195.77</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl75 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl74 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">7.14</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">27.03</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">5.95</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">32.93</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">$ 3.87 </TD><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1"></TD><TD class=xl81 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl83 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1">$ 27.63 </TD></TR></TBODY></TABLE>

what i am looking to try to do is remove the blank columns and resize the columns with data...
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
This procedure DeleteAllBlankColumns (Note a Function LastColInWS called from within the Sub) will get rid of any Blank Columns.

Good Luck..


Code: 
Sub DeleteAllBlankColumns()
Dim LColNum As Long
Dim Rng As Range
Dim C As Range
Dim SelCol As Long
With ActiveSheet
        LColNum = LastColInWS
End With
With ActiveSheet
For i = LColNum To 1 Step -1
    If Application.WorksheetFunction.CountA(.Columns(i)) = 0 Then
    .Columns(i).Delete
    End If

Next i
End With

End Sub

Function LastColInWS()
Dim sh As Worksheet
Application.Volatile
    On Error Resume Next
    Set sh = ActiveSheet
    LastColInWS = sh.Cells.Find(What:="*", _
                            After:=sh.Cells(Rows.Count, Columns.Count), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function
 
Upvote 0
Wow... :) that works great!!! Thank you

you wouldn't know how to incorporate that into my filter code i have so that i dont have to manually run this new one everytime would you?
other then that, resizeing the existing columns with data...
 
Last edited:
Upvote 0
Just noticed your code -- in more detail..
You have these 2 procedures as Functions; I would have thought they shoud be Sub(s)
instead; Confused here !!!

Function FilterAndCopy(rng As Range, Choice As String, Field As String)
Function CreateSheet(Choice As String)
 
Upvote 0
that was the only way i could get it to work with my userform...
not sure if it would make more sence to you if i added the second half of the search...

UserForm:
Code: 
Option Explicit
Private Sub ComboBox1_Change()
End Sub
Private Sub CommandButton1_Click()
Dim Rng As Range
Dim ctrl As MSForms.Control
Dim Field As String
Field = ComboBox1.ListIndex + 1
'Set Error Handling
On Error GoTo ws_exit:
Application.EnableEvents = False
'Set Range
Set Rng = ActiveSheet.UsedRange
For Each ctrl In UserForm1.Controls
    If Left(ctrl.Name, 4) = "Text" Then
       If ctrl.Value <> "" Then
            CreateSheet ctrl.Value
            FilterAndCopy Rng, ctrl.Value, Field
            Rng.AutoFilter
        End If
     End If
Next
Unload Me
Exit Sub
ws_exit:
    Set Rng = Nothing
    Application.EnableEvents = True
    Unload Me
End Sub
Private Sub CommandButton2_Click()
'Cancel Button
Unload Me
End Sub

Private Sub Label18_Click()
End Sub
Private Sub UserForm_Initialize()
Dim FillRange       As Range
Dim Cel             As Range
Dim iLastRow        As Long
Dim iLastColumn     As Long
 'Find Last Row
iLastRow = 1
 'Find Last Column
iLastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
 'Set Range from A1 to Last Row/Column
Set FillRange = Range("A1", Cells(iLastRow, iLastColumn))
            
For Each Cel In FillRange
    Me.ComboBox1.AddItem Cel.Text
Next
    
ComboBox1.ListIndex = 0
    
Set Cel = Nothing
Set FillRange = Nothing
End Sub

Search:
Code: 
Option Explicit
Sub formshow()
'Show Search Form
UserForm1.Show
End Sub
Function FilterAndCopy(Rng As Range, Choice As String, Field As String)
    
    Dim FiltRng As Range
    Worksheets(Choice).Cells.ClearContents
    Rng.AutoFilter Field:=Field, Criteria1:=Choice
    On Error Resume Next
    Set FiltRng = Rng.SpecialCells(xlCellTypeVisible).EntireRow
    On Error GoTo 0
    FiltRng.Copy Worksheets(Choice).Range("A1")
    Set FiltRng = Nothing
     
End Function
Function CreateSheet(Choice As String)
Dim NewSheet As Worksheet
On Error GoTo Err:
Worksheets(Choice).Select
Exit Function
Err:
Set NewSheet = Worksheets.Add
    On Error Resume Next
    NewSheet.Name = Choice
    On Error GoTo 0
End Function

I honestly still can't seem to incorperate your Column removal code with my new tab that is created... i could send you my workbook if you wanted to take a closer look at it...

Thanks for your help so far...
 
Upvote 0
Try this:
Code: 
Option Explicit
Sub formshow()
    UserForm1.Show
End Sub
 
Function FilterAndCopy(rng As Range, Choice As String, Field As String)
    Dim iCol        As Long
 
    rng.AutoFilter Field:=Field, Criteria1:=Choice
 
    With Worksheets(Choice).UsedRange
        .ClearContents
        rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy .Range("A1")
 
        For iCol = .Columns.Count To 1 Step -1
            If WorksheetFunction.CountA(.Columns(iCol)) = 0 Then
                .Columns(iCol).EntireColumn.Delete
            End If
        Next iCol
    End With
End Function
 
Function CreateSheet(Choice As String)
    On Error GoTo Oops
    Worksheets(Choice).Select
    Exit Function
 
Oops:
    Err.Clear
    Sheets.Add.Name = Choice
End Function
 
Upvote 0
this has the same effect as the original code.. it creates the new tab with all the correct Data but doesn't remove the empty columns :(
 
Upvote 0
I have stepped through... it searches just fine and finds the content creates the new tab pastes the info into A1 and thats where it sits... blank columns are not removed... no errors reported at all...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
T
  • Question Question
Exporting Cell Range As PNG - Getting A White Border To The Bottom & Right - Why?
2
Replies
16
Views
174
jolivanes
jolivanes
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A

Forum statistics

Threads
1,224,518
Messages
6,179,253
Members
452,900
Latest member
LisaGo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top