Houdini Cells

ChrisLatham

Board Regular
Joined
Jan 13, 2009
Messages
59
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:

Code:
'// 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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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

Board Regular
Joined
Jan 13, 2009
Messages
59
Hey mate, I gave up and started again lol.

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

Thanks for your time.
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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

Code:
    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

Legend
Joined
May 15, 2003
Messages
29,536
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:

Code:
    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:

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

HTH,
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,087
Members
414,501
Latest member
mdhaumyu

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
Top