Need to speed up this code, tidy it up and stop the repeating of each cells

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
76
I have a few worksheets that uses the same code for each, it really slows down the macro, Ive been going through all sorts of code from various sources but I just cant translate it to VB code that would work.
There has to be a shorter more beneficent way to condense this code. Ive been looking at arrays, but I dont know how to fit this code into it.

Please can anyone help.


Sheets("imported").Select
Columns("A:A").Select
Selection.NumberFormat = "@"
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("B:B").Select
Selection.NumberFormat = "@"
Columns("C:C").Select
Selection.NumberFormat = "General"
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("D:D").Select
Selection.NumberFormat = "General"
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("E:F").Select
Selection.NumberFormat = "dd.mm.yyyy"
Columns("G:G").Select
Selection.NumberFormat = "General"
Columns("H:H").Select
Selection.NumberFormat = "General"
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("I:I").Select
Selection.NumberFormat = "General"
Columns("J:J").Select
Selection.NumberFormat = "General"
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("K:K").Select
Selection.NumberFormat = "General"
Columns("L:N").Select
Selection.NumberFormat = "dd.mm.yyyy"
Columns("O:AR").Select
Selection.NumberFormat = "General"
Columns("AS:AS").Select
Selection.NumberFormat = "dd.mm.yyyy"
Columns("AT:AU").Select
Selection.NumberFormat = "General"
Columns("A:AU").Select
ActiveSheet.Range("A:AU").RemoveDuplicates Columns:=Array(1, 2, 3, 16), _
Header:=xlYes
Columns("AT:AT").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,040
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Arrays won't do you any good there as you aren't transferring any data.
What will make a bit of difference is removing all those Select/Selections and turning off screenupdating.

Try the code below but it is untested so test it on a copy of your workbook.

VBA Code:
Sub Dave01()
    Application.ScreenUpdating = False
   
    With Sheets("imported")
        With .Columns("A:A")
            .NumberFormat = "@"
            .Replace What:=" ", Replacement:="", LookAt:=xlPart, _
                     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                     ReplaceFormat:=False
        End With
        .Columns("B:B").NumberFormat = "@"
       
        With .Columns("C:C")
            .NumberFormat = "General"
            .Replace What:=" ", Replacement:="", LookAt:=xlPart, _
                     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                     ReplaceFormat:=False
        End With
       
        With .Columns("D:D")
            .NumberFormat = "General"
            .Replace What:=" ", Replacement:="", LookAt:=xlPart, _
                     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                     ReplaceFormat:=False
        End With
        .Columns("E:F").NumberFormat = "dd.mm.yyyy"
        .Columns("G:G").NumberFormat = "General"
       
        With .Columns("H:H")
            .NumberFormat = "General"
            .Replace What:=" ", Replacement:="", LookAt:=xlPart, _
                     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                     ReplaceFormat:=False
        End With
        .Columns("I:I").NumberFormat = "General"
       
        With .Columns("J:J")
            .NumberFormat = "General"
            .Replace What:=" ", Replacement:="", LookAt:=xlPart, _
                     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                     ReplaceFormat:=False
        End With
       
        .Columns("K:K").NumberFormat = "General"
        .Columns("L:N").NumberFormat = "dd.mm.yyyy"
        .Columns("O:AR").NumberFormat = "General"
        .Columns("AS:AS").NumberFormat = "dd.mm.yyyy"
        .Columns("AT:AU").NumberFormat = "General"
       
        .Range("A:AU").RemoveDuplicates .Columns:=Array(1, 2, 3, 16), _
        Header:=xlYes
        .Columns("AT:AT").Replace What:=" ", Replacement:="", LookAt:=xlPart, _
                                  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                                  ReplaceFormat:=False

        With Application
            .Goto Sheets("imported").Range("A1")
            .ScreenUpdating = True
        End With
    End With
End Sub
 
Solution

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
76
perfect, its a lot tidy than the one I have, so much easier to follow too.

Thank you.

The screen updating False true is at the begging and the end.

as well as these

Application.CutCopyMode = False 'Clearing the Office Clipboard

Application.ScreenUpdating = False
Application.EnableEvents = False '
ActiveSheet.DisplayPageBreaks = False
Application.Calculation = xlManual '

I inherited the code which works with SAP Scripting, so Im working my way through it seeing what I can make run faster.

thanks for your help.

Dave.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,368
Messages
5,635,856
Members
416,886
Latest member
coreyalaurence37

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