Houdini Cells

[ChrisLatham]

Below is a segment of code from a Macro I have created, it's a bit rough etc but that may or may not be the problem.

When this code runs, the first cell of each column which I concatenate seems to end up getting deleted. I've pasted part of it below, can anyone point out what could be causing my problem?

Thanks in advance:

'// Begin Sheet2 Process
    Sheets("Sheet2").Activate
    Range("A3").Select
    Range("A3:AU156").Sort Key1:=Range("Q5"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Range("D3").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(RC[2]&"", ""&RC[3]&"", ""&RC[4]&"", ""&RC[5])"
    Range("D3").Select
    Selection.AutoFill Destination:=Range("D3:D10000"), Type:=xlFillDefault
    Range("D3:D10000").Select
    Columns("D:D").Select
    Selection.Copy
    Columns("E:E").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("E:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").EntireColumn.AutoFit
    Range("D1").Select
    Cells.Select

 

[Mister H]

Hi ChrisLatham:

I am not sure what is causing your problem but I was wondering if you have tried stepping through your code using F8 and watching your Excel sheet as you hit F8 so you can see what is happening. This may help to pinpoint the line of code that is causing your problem,. Post back if you are unsure as to what I am referring to regarding Stepping through your code.

Good Luck,
Mark

 

[ChrisLatham]

Hey mate, I gave up and started again lol.

Think everything is fine now, we'll see once people start using it.

Thanks for your time.

 

[dave3009]

Hi ChrisLatham

You can clean up your code quite a bit by taking out all those Selects and stuff

This is what it might look like

    With Sheets("Sheet2")
    .Range("A3:AU156").Sort Key1:=Range("Q5"), Order1:=xlAscending
    .Columns("A:B").Delete Shift:=xlToLeft
    .Columns("D:D").Insert Shift:=xlToRight
    .Columns("D:D").Insert Shift:=xlToRight
    .Range("D3:D10000").FormulaR1C1 = "=CONCATENATE(RC[2]&"", ""&RC[3]&"", ""&RC[4]&"", ""&RC[5])"
    .Columns("D:D").Copy
    .Columns("E:E").PasteSpecial xlPasteValues
    .Columns("D:D").Delete Shift:=xlToLeft
    .Columns("E:H").Delete Shift:=xlToLeft
    .Columns("D:D").EntireColumn.AutoFit
    End With

 

[Smitty]

Note that it's generally not necessary to select objects in order to work with them, when you see "Select" followed by "Selection" you can usually eliminate both statements. So this:

    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Range("D3").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(RC[2]&"", ""&RC[3]&"", ""&RC[4]&"", ""&RC[5])"

Can be this:

    Columns("D:D").Insert Shift:=xlToRight
    Range("D3").FormulaR1C1 = _
        "=CONCATENATE(RC[2]&"", ""&RC[3]&"", ""&RC[4]&"", ""&RC[5])"

HTH,