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

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
116
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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
Back
Top