Reordering columns shouldn't be this hard!

Jimbob2000

New Member
Joined
Jun 27, 2019
Messages
25
I have this macro that should reorder the columns on a report that downloads from a database. Except, it just seems to crash excel whenever I run it. I've tested the individual parts of the macro separately, and they seem to work fine, but all together... nope.

Any ideas would be gratefully received!



VBA Code:
Sub CAC_No_Download()

'This Macro takes the MKF database referrals report and processes it ready to use on the DAC template

'

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Gives the active worksheet a nice name
Dim dwn As Worksheet
Set dwn = ActiveSheet

'Formats the cells, unmerges, unhides, and deletes the top 7 rows because they're sorta junk
dwn.Columns.EntireColumn.Hidden = False
dwn.Rows.EntireRow.Hidden = False
dwn.Cells.UnMerge
dwn.Rows("1:7").Delete
dwn.Range("A:Z").ColumnWidth = 15
dwn.Rows.RowHeight = 12.75

'Reformats some of the columns
With dwn.Range("N:N")
    .NumberFormat = "General"
    .Value = .Value
End With

With dwn.Range("Q:Q")
    .NumberFormat = "General"
    .Value = .Value
End With

'Renames the locator info column
dwn.Range("I1").Value = "Locator Info"

'Now we put the columns in the right order...
'Locator Info
dwn.Range("I:I").Copy
dwn.Range("A:A").Insert

'ZIP
dwn.Range("O:O").Copy
dwn.Range("A:A").Insert

'First Name
dwn.Range("E:E").Copy
dwn.Range("A:A").Insert

'Last Name
dwn.Range("E:E").Copy
dwn.Range("A:A").Insert

'Referral Date
dwn.Range("H:H").Copy
dwn.Range("A:A").Insert

'Custom Case ID
dwn.Range("V:V").Copy
dwn.Range("A:A").Insert

'This clears the contents of the other columns
With dwn.Range("G:AZ")
    .ClearContents
    .ClearFormats
End With

'Next we resort the data by CAC#
With dwn.Sort.SortFields
        .Clear
        .Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
End With
        
    With dwn.Sort
        .SetRange Range("A:G")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

'And now we apply a formula to extract the locator number from column F...
dwn.Range("G2:G10000").FormulaR1C1 = _
        "=IFERROR(LEFT(RC[-1],FIND(""*"",RC[-1])-1),""0"")"
        
'And give column G a nice header
dwn.Range("G1").Value = "Locator"

'This turns the screen updating back on
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Application.CutCopyMode = False

'And this selects and copies what we've done ready to paste it into the DAC template
Dim LastRow As Long
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Range("A2:G" & LastRow).Copy

Range("H1").Value = ":-)"

End Sub
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
Have you tried stepping through the code line-by-line to see exactly where things seem to go bad?
 

Jimbob2000

New Member
Joined
Jun 27, 2019
Messages
25
Have you tried stepping through the code line-by-line to see exactly where things seem to go bad?
Yes... weirdly, when I do that, it works just fine. It seems like trying to do everything at once overloads the macro or something.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,146
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Have you looked at doing this with Power Query? Post a sample worksheet with XL2BB and a mocked up solution to see how that works for you.
 

Jimbob2000

New Member
Joined
Jun 27, 2019
Messages
25

ADVERTISEMENT

Have you looked at doing this with Power Query? Post a sample worksheet with XL2BB and a mocked up solution to see how that works for you.
I haven't ... I'm not familiar with queries... I've done a bit of investigating, and it seems to be a useful option. It doesn't seem as automatic as doing it with VBA, though. At least, I don't know how to automate it.
 

Jimbob2000

New Member
Joined
Jun 27, 2019
Messages
25
I think I figured out what was going wrong... I was using the clipboard a ton in the macro (.copy and .insert) and that was using up too much resource for my poor little computer :) so I inserted some blank columns and used the .value = .value method to make the new columns equal the columns I wanted, in the right order, and without using the clipboard.

Thanks for all your help and suggestions though! I'm going to take a look at queries because they seem pretty useful!
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,146
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
To learn more about PQ and its ability to update files, look at acquiring "M is for (Data) Monkey" by Ken Puls and Miguel Escobar. Available in the MrE store or Amazon.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

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