Rid myself of .Select Selection

P

PhosFeedLogisticsMan

New Member
Joined
Aug 9, 2017
Messages
17
I don't get why this is not working, but I am trying to clean up and speed up my code.

I currently have this and it works fine within my sub.
Code: 
Columns("O:Q").Select
    Selection.ColumnWidth = 35.01
    Columns("A").Select
    Selection.ColumnWidth = 6.01
    Columns("B").Select
    Selection.ColumnWidth = 10.01
    Columns("C").Select
    Selection.ColumnWidth = 25.01
    Columns("D").Select
    Selection.ColumnWidth = 6.01
    Columns("E").Select
    Selection.ColumnWidth = 28.01
    Columns("F").Select
    Selection.ColumnWidth = 25.01
    Columns("G").Select
    Selection.ColumnWidth = 10.01
    Columns("H").Select
    Selection.ColumnWidth = 5.01
    Columns("I").Select
    Selection.ColumnWidth = 25.01
    Columns("J").Select
    Selection.ColumnWidth = 20.01
    Columns("K").Select
    Selection.ColumnWidth = 5.01
    Columns("L").Select
    Selection.ColumnWidth = 5.01
    Columns("M").Select
    Selection.ColumnWidth = 5.01
    Columns("N").Select
    Selection.ColumnWidth = 10.01
    Columns("O").Select
    Selection.ColumnWidth = 35.01
    Columns("P").Select
    Selection.ColumnWidth = 25.01
    Columns("O:O").Select
    Selection.WrapText = True
    Selection.MergeCells = False



However when I replace it with:

Code: 
    Columns("O:Q").ColumnWidth = 35.01
    Columns("A").ColumnWidth = 6.01
    Columns("B").ColumnWidth = 10.01
    Columns("C").ColumnWidth = 25.01
    Columns("D").ColumnWidth = 6.01
    Columns("E").ColumnWidth = 28.01
    Columns("F").ColumnWidth = 25.01
    Columns("G").ColumnWidth = 10.01
    Columns("H").ColumnWidth = 5.01
    Columns("I").ColumnWidth = 25.01
    Columns("J").ColumnWidth = 20.01
    Columns("K").ColumnWidth = 5.01
    Columns("L:M").ColumnWidth = 5.01
    Columns("N").ColumnWidth = 10.01
    Columns("O").ColumnWidth = 35.01
    Columns("P").ColumnWidth = 25.01
    Columns("O").WrapText = True
   .MergeCells = False
Or this:

Code: 
    Columns("O:Q").ColumnWidth = 35.01
    Columns("A:A").ColumnWidth = 6.01
    Columns("B:B").ColumnWidth = 10.01
    Columns("C:C").ColumnWidth = 25.01
    Columns("D:D").ColumnWidth = 6.01
    Columns("E:E").ColumnWidth = 28.01
    Columns("F:F").ColumnWidth = 25.01
    Columns("G:G").ColumnWidth = 10.01
    Columns("H:H").ColumnWidth = 5.01
    Columns("I:I").ColumnWidth = 25.01
    Columns("J:J").ColumnWidth = 20.01
    Columns("K:K").ColumnWidth = 5.01
    Columns("L:L").ColumnWidth = 5.01
    Columns("M:M").ColumnWidth = 5.01
    Columns("N:N").ColumnWidth = 10.01
    Columns("O:O").ColumnWidth = 35.01
    Columns("P:P").ColumnWidth = 25.01
    Columns("O:O").WrapText = True
    Columns("O:O").MergeCells = False

It will not format the columns. Trying to speed this thing up by getting rid of Select and Selection. What am I doing wrong?
 
Last edited by a moderator:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Just out of interest what happens if you change Sheet1 in the code below to whatever your sheets name is?

Code: 
Sub quessit()
    With Sheets("Sheet1")
        .Columns("O:Q").ColumnWidth = 35.01
        .Columns("A").ColumnWidth = 6.01
        .Columns("B").ColumnWidth = 10.01
        .Columns("C").ColumnWidth = 25.01
        .Columns("D").ColumnWidth = 6.01
        .Columns("E").ColumnWidth = 28.01
        .Columns("F").ColumnWidth = 25.01
        .Columns("G").ColumnWidth = 10.01
        .Columns("H").ColumnWidth = 5.01
        .Columns("I").ColumnWidth = 25.01
        .Columns("J").ColumnWidth = 20.01
        .Columns("K").ColumnWidth = 5.01
        .Columns("L:M").ColumnWidth = 5.01
        .Columns("N").ColumnWidth = 10.01
        .Columns("O").ColumnWidth = 35.01
        .Columns("P").ColumnWidth = 25.01
        With .Columns("O")
            .WrapText = True
            .MergeCells = False
        End With
    End With
End Sub
 
Upvote 0
You can also shorten it by looping through the values in an array:

Code: 
Sub test()
Dim x, y As Long
x = Array(6.01, 10.01, 25.01, 6.01, 28.01, 25.01, 10.01, 5.01, 25.01, 20.01, 5.01, 5.01, 10.01, 35.01, 25.01, 35.01, 35.01)
For y = 1 To 17
    With Worksheets("Sheet1")
        .Columns(y).ColumnWidth = x(y - 1)
        .Columns("O").WrapText = True
        .Columns("O").MergeCells = False
    End With
Next
End Sub
 
Last edited:
Upvote 0
That may be part of the problem, as I can't specify just one sheet as they will be different everytime I pull new data. Again, not sure how to fix. Within my sub, I am calling another sub.... which is creating new sheets and grabbing the data from another sheet.....the sub I'm calling looks like this:
Code: 
Sub sheetsarrayNO()
    
    Dim ShtLst() As Variant
    
    Dim SourceSheet As Worksheet
    Dim TargetSheet As Worksheet
    Dim SheetNames() As Variant
    Dim i As Long
    Dim LR As Long
    
    With Sheets("SCAC_Codes")
        SheetNames = .Range("A1", .Range("A1").End(xlDown)).Value
    End With
    'EDIT
    Set SourceSheet = Sheets("NewOrders")
    Const FilterColumn = 1
    'END EDIT
    With SourceSheet
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
    
        For i = LBound(SheetNames) To UBound(SheetNames)
            Set TargetSheet = Worksheets(Application.Transpose(SheetNames)(i))
            TargetSheet.Cells.ClearContents
            
            With .Range("A2:Q" & LR)
                .AutoFilter Field:=FilterColumn, Criteria1:=Application.Transpose(SheetNames)(i)
                .Offset(0, 0).Copy TargetSheet.Range("A1")
                
            End With
        Next i
    End With
End Sub

Then formatting was being applied back in the initial sub.
 
Last edited by a moderator:
Upvote 0
Couldn't figure out how to delete the above thread. Actually within my sub, I call this sub

Code: 
Dim ShtLst() As Variant
    
    With Sheets("SCAC_Codes")
        ShtLst = .Range("A1", .Range("A1").End(xlDown)).Value
    End With
    Sheets(Application.Transpose(ShtLst)).Select

Sorry, relatively new to VBA, so I'm still learning some very basic things as I go. Its selecting all the Sheets where data was just copied and sheets created.
 
Last edited:
Upvote 0
Yep, your original code had selected a specific sheet first, then started applying the column widths.
Without that, then the code will only apply to whichever sheet happens to be active.

In the last code posted, I assume ShtLst is a list of sheet names you want this applied to?
You just need to create a variable and loop through that list.

Try this
Code: 
Dim ShtLst() As Variant, ws As Variant
    
With Sheets("SCAC_Codes")
    ShtLst = .Range("A1", .Range("A1").End(xlDown)).Value
End With
'Sheets(Application.Transpose(ShtLst)).Select
For Each ws In ShtLst
    With Sheets(ws)
        .Columns("O:Q").ColumnWidth = 35.01
        .Columns("A").ColumnWidth = 6.01
        .Columns("B").ColumnWidth = 10.01
        .Columns("C").ColumnWidth = 25.01
        .Columns("D").ColumnWidth = 6.01
        .Columns("E").ColumnWidth = 28.01
        .Columns("F").ColumnWidth = 25.01
        .Columns("G").ColumnWidth = 10.01
        .Columns("H").ColumnWidth = 5.01
        .Columns("I").ColumnWidth = 25.01
        .Columns("J").ColumnWidth = 20.01
        .Columns("K").ColumnWidth = 5.01
        .Columns("L:M").ColumnWidth = 5.01
        .Columns("N").ColumnWidth = 10.01
        .Columns("O").ColumnWidth = 35.01
        .Columns("P").ColumnWidth = 25.01
        .Columns("O").WrapText = True
        .Columns("O").MergeCells = False
    End With
Next ws
 
Last edited:
Upvote 0
Jonmo1 said:
Yep, your original code had selected a specific sheet first, then started applying the column widths.
Without that, then the code will only apply to whichever sheet happens to be active.

In the last code posted, I assume ShtLst is a list of sheet names you want this applied to?
You just need to create a variable and loop through that list.

Try this
Code: 
Dim ShtLst() As Variant, ws As Variant
    
With Sheets("SCAC_Codes")
    ShtLst = .Range("A1", .Range("A1").End(xlDown)).Value
End With
'Sheets(Application.Transpose(ShtLst)).Select
For Each ws In ShtLst
    With Sheets(ws)
        .Columns("O:Q").ColumnWidth = 35.01
        .Columns("A").ColumnWidth = 6.01
        .Columns("B").ColumnWidth = 10.01
        .Columns("C").ColumnWidth = 25.01
        .Columns("D").ColumnWidth = 6.01
        .Columns("E").ColumnWidth = 28.01
        .Columns("F").ColumnWidth = 25.01
        .Columns("G").ColumnWidth = 10.01
        .Columns("H").ColumnWidth = 5.01
        .Columns("I").ColumnWidth = 25.01
        .Columns("J").ColumnWidth = 20.01
        .Columns("K").ColumnWidth = 5.01
        .Columns("L:M").ColumnWidth = 5.01
        .Columns("N").ColumnWidth = 10.01
        .Columns("O").ColumnWidth = 35.01
        .Columns("P").ColumnWidth = 25.01
        .Columns("O").WrapText = True
        .Columns("O").MergeCells = False
    End With
Next ws
Click to expand...


That did it JonMo1. Appreciate it. This works perfectly.
 
Upvote 0
Scott Huish said:
You can also shorten it by looping through the values in an array:

Code: 
Sub test()
Dim x, y As Long
x = Array(6.01, 10.01, 25.01, 6.01, 28.01, 25.01, 10.01, 5.01, 25.01, 20.01, 5.01, 5.01, 10.01, 35.01, 25.01, 35.01, 35.01)
For y = 1 To 17
    With Worksheets("Sheet1")
        .Columns(y).ColumnWidth = x(y - 1)
        .Columns("O").WrapText = True
        .Columns("O").MergeCells = False
    End With
Next
End Sub
Click to expand...
Actually, you can shorten it some more while speeding it up in the process (not that anyone would see the speedup, of course) by getting rid of the loop and eliminating the variable...
Code: 
[table="width: 500"]
[tr]
	[td]Sub Test2()
  With Worksheets("Sheet1")
    .Range("A1:Q1").ColumnWidth = Array(6.01, 10.01, 25.01, 6.01, 28.01, 25.01, 10.01, 5.01, 25.01, 20.01, 5.01, 5.01, 10.01, 35.01, 25.01, 35.01, 35.01)
    .Columns("O").WrapText = True
    .Columns("O").MergeCells = False
  End With
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Very minor change in code had a dramatic impact on the speed. Why?
Replies
2
Views
616
deZine
D
G
  • Solved
Index Match & round vs specific cell by cell calculation
Replies
5
Views
331
Fluff
Fluff
S
  • Solved
Optimizing Macro
Replies
8
Views
1K
sax2play
S
W
  • Solved
Macro works when ran on sheet1 but not when ran from sheet2
Replies
2
Views
445
Welearnaswego
W
A
  • Solved
every time I run this code the auto filter only filters 1 column any ideals
Replies
1
Views
556
HaHoBe
HaHoBe

Forum statistics

Threads
1,215,577
Messages
6,125,637
Members
449,242
Latest member
Mari_mariou

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