Solved: Column selection not working properly

[boc_est1986]

Heres my code. On anything selecting columns excell is selecting all columns instead of just the column asked for. I can't see why. Any help would be appreciated.

Cells.Select
Selection.Copy
Windows("8D KPI").Activate
Sheets("RAW DATA Closed").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("A2:Z3").Select
With Selection
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A3:N3").Select
Selection.Delete Shift:=xlUp
Range("O2:X2").Select
Selection.Delete Shift:=xlUp
Range("Y3:Z3").Select
Selection.Delete Shift:=xlUp
Columns("E").Select
Selection.Delete Shift:=xlToLeft
Columns("M").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Rows("2:2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:="<>Procurement", Operator:= _
xlAnd
Rows("5:5").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1").Select
Selection.AutoFilter Field:=8
Columns("M").Select
Selection.Insert Shift:=xlToRight
Range("M3").Select
ActiveCell.FormulaR1C1 = "=""D""&RC[1]"
Range("M3").Select
Selection.AutoFill Destination:=Range("M3:M200")
Range("M3:M200").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("M2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "D"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Columns("N").Select
Selection.Delete Shift:=xlToLeft
Columns("N").Select
Selection.Insert Shift:=xlToRight
Range("N2").Select
ActiveCell.FormulaR1C1 = "D3 Comleted"
With ActiveCell.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("N3").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-11]"
Range("N3").Select
Selection.AutoFill Destination:=Range("N3:N21")
Range("N3:N21").Select
Selection.NumberFormat = "0"
Range("N2").Select

[Bob Phillips]

I don't get this code, part way through you delete all rows of data from row 5 down, then you try and set borders on that selection (which has just been deleted). How does that compute?

[Aflatoon]

Do you have any merged cells in the columns you are selecting?

You very rarely require to select anything in code in order to manipulate it and it is more efficient to avoid doing that when possible. For example:
Range("A3:N3").Select
Selection.Delete Shift:=xlUp
Range("O2:X2").Select
Selection.Delete Shift:=xlUp
Range("Y3:Z3").Select
Selection.Delete Shift:=xlUp
Columns("E").Select
Selection.Delete Shift:=xlToLeft
Columns("M").Select
Selection.Delete Shift:=xlToLeft
can be reduced to this:

Range("A3:N3").Delete Shift:=xlUp
Range("O2:X2").Delete Shift:=xlUp
Range("Y3:Z3").Delete Shift:=xlUp
Columns("E").Delete Shift:=xlToLeft
Columns("M").Delete Shift:=xlToLeft

[boc_est1986]

thanks for the replies. I know my coding is aweful, the border formating was to remove borders not add them.
It was a merged cell withing the selection that i hadn't noticed

[Bob Phillips]

I had a quick shot at tidying the code. You will need to test, but oit is a lot clearer



Cells.Copy
With Worksheets("RAW DATA Closed")

.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

With .Range("A1")

With .Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
End With
End With

.Range("A2:Z3").WrapText = True
.Range("A3:N3").Delete Shift:=xlUp
.Range("O2:X2").Delete Shift:=xlUp
.Range("Y3:Z3").Delete Shift:=xlUp
.Columns("E").Delete Shift:=xlToLeft
.Columns("M").Delete Shift:=xlToLeft

With .Rows("2:2")

.AutoFilter
.AutoFilter Field:=8, Criteria1:="<>Procurement", Operator:=xlAnd
End With

.Range("A5", Cells(Rows.Count, "A").End(xlUp)).EntireRow.Delete Shift:=xlUp

With Selection

.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With

.Range("A1").AutoFilter Field:=8
.Columns("M").Insert Shift:=xlToRight
.Range("M3:M200").FormulaR1C1 = "=""D""&RC[1]"

With .Range("M3:M200")

.Value = .Value
End With

With .Range("M2")

.Value2 = "D"
With .Font

.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
End With
End With

.Columns("N").Delete Shift:=xlToLeft
.Columns("N").Insert Shift:=xlToRight

With .Range("N2")

.Value2 = "D3 Completed"
With .Font

.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
End With
End With

With .Range("N3:N21")

.FormulaR1C1 = "=RC[-2]-RC[-11]"
.NumberFormat = "0"
End With

.Range("N2").Select
End With

[boc_est1986]

thanks worked a treat. i really should get in the habit of writing cleaner code