Delete all spaces in columns "A", "B" and "C".

H

harzer

Board Regular
Joined
Dec 15, 2021
Messages
122
Office Version
  1. 2016
Platform
  1. Windows
Hello everyone,
My data is in columns: "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" and "K" " .
As I can not find a solution to my problem, I submit it to you, hoping that a brave among you could help me.
The code that I am asking you will process the data that is only on the cells of the first three columns: "A", "B" and "C".
Additional info: The first line of my data is the headers.

What should this code do?
This code must go through all the cells of the three columns: "A", "B" and "C". if it finds any space (" ") in the cells of columns "A", "B" and "C", then it deletes them.
In summary, we remove all the spaces whether they are at the beginning, or inside or at the end of the cells, in other words, we must replace the character " " by "".
I take the liberty of asking you for a fairly quick code because my data is important.
I remain at your disposal for any further information.
Thanks in advance for your contributions.

The start data

Classeur1
ABCDEFGHIJK
1JeunePèreMèreEleveurAgeVolièreCageNé(e)ToursInformationElevage
2 AEY27-0 01/20 23M AEY2 7-001/2 023MAEY27-0 01/2023MGérard Claude0a 1m 11j5H15450414T
3AE Y27-0 02/20 23MAEY2 7-00 2/202 3MAEY27-002 /2023MGérard Claude0a 1m 3j5H11450474Tx
4A EY27-00 3/202 3MAEY2 7 - 0 0 3 / 2 0 23M AEY2 7-003/2023M Gérard Claude0a 0m 31j5H12450514T
5A EY 27-004 /2023 M AEY 27-0 04/2 023MAE Y 27-004 /2023MGérard Claude0a 0m 15j5H5450674Tx
6AE Y27-0 05/2023M A EY 27- 005/ 202 3MAE Y27-00 5/202 3MGérard Claude0a 0m 15j5H5450674T
7 AEY27-006/2023MAE Y27-0 0 6/20 23M AEY27-006/ 2023M Gérard Claude0a 0m 15j5H5450674Tx
8AE Y27-007/202 3M AEY 27-007/2023MAEY27- 007/2023MGérard Claude0a 0m 15j5H5450674T
9AEY 27-008/2 023MAE Y27-008/2023MAEY27-008/2023MGérard Claude0a 0m 15j5H5450674T
10A EY27-009/2023FAEY27-009/20 23FAEY27-009/2023FGérard Claude0a 0m 14j4B6450684T
11AEY27-0 10/20 23FAEY27 -010/2023FAEY27-010/2023FGérard Claude0a 0m 14j4B6450684Tx
12AEY 27-011/2023FAEY27-011/2023FAEY27-011/2023FGérard Claude0a 0m 14j4B6450684Tx
13AEY27-0 12/2023MAEY27-01 2/2023MAEY27-012/2023MGérard Claude0a 0m 13j5H13450694Tx
14AEY27-013/2023M AEY27-0 13/2023MAEY27-013/2023MGérard Claude0a 0m 13j5H13450694Tx
15AEY27-014 /2023FA EY27-014/2023FAEY27-014/2023FGérard Claude0a 0m 13j4B13450694Tx
16AEY27-015/ 2023F AEY27-015/2023FAEY27-015/2023FGérard Claude0a 0m 11j4B7450714T
17AEY27-0 16/2023FAEY27-016 /2023FAEY27-016/2023FGérard Claude0a 1m 11j4B15450414T
18AEY 27 -017/ 2023MAE Y27-017/2 023MAEY27- 017/ 2023MGérard Claude0a 1m 3j5H11450474T
19AEY27-018/ 2023MAEY2 7-018/202 3MAE Y27-0 18/2023MGérard Claude0a 0m 31j5H12450514Tx
20AEY27-019/2023FAE Y27-019 /2023FAEY27-01 9 / 2 0 2 3 F Gérard Claude0a 0m 15j4B5450674T
21AEY27-020/2023MAEY27-020 /2023MA EY 27 -02 0/2 023 MGérard Claude0a 0m 15j5H5450674Tx
22AEY27- 021/2023FAE Y2 7-021/2023FAEY2 7-021/2023FGérard Claude0a 0m 15j4B5450674T
23AEY27- 022/2023MAEY 2 7 - 0 2 2/2023MAEY27- 022/2023MGérard Claude0a 0m 15j5H5450674T
24AEY27-023/2023M A E Y 2 7 - 0 2 3 / 2 0 2 3 M AEY27-023 /2023MGérard Claude0a 0m 15j5H5450674Tx
25AEY27-024 /2023MA EY27-024/2023M A E Y 2 7 - 0 2 4 / 2 0 2 3 M Gérard Claude0a 0m 14j5H6450684Tx
Parents


Unless I am mistaken, here is the desired result

Classeur1
ABCDEFGHIJK
1JeunePèreMèreEleveurAgeVolièreCageNé(e)ToursInformationElevage
2AEY27-001/2023MAEY27-001/2023MAEY27-001/2023MGérard Claude0a 1m 11j5H15450414T
3AEY27-002/2023MAEY27-002/2023MAEY27-002/2023MGérard Claude0a 1m 3j5H11450474Tx
4AEY27-003/2023MAEY27-003/2023MAEY27-003/2023MGérard Claude0a 0m 31j5H12450514T
5AEY27-004/2023MAEY27-004/2023MAEY27-004/2023MGérard Claude0a 0m 15j5H5450674Tx
6AEY27-005/2023MAEY27-005/2023MAEY27-005/2023MGérard Claude0a 0m 15j5H5450674T
7AEY27-006/2023MAEY27-006/2023MAEY27-006/2023MGérard Claude0a 0m 15j5H5450674Tx
8AEY27-007/2023MAEY27-007/2023MAEY27-007/2023MGérard Claude0a 0m 15j5H5450674T
9AEY27-008/2023MAEY27-008/2023MAEY27-008/2023MGérard Claude0a 0m 15j5H5450674T
10AEY27-009/2023FAEY27-009/2023FAEY27-009/2023FGérard Claude0a 0m 14j4B6450684T
11AEY27-010/2023FAEY27-010/2023FAEY27-010/2023FGérard Claude0a 0m 14j4B6450684Tx
12AEY27-011/2023FAEY27-011/2023FAEY27-011/2023FGérard Claude0a 0m 14j4B6450684Tx
13AEY27-012/2023MAEY27-012/2023MAEY27-012/2023MGérard Claude0a 0m 13j5H13450694Tx
14AEY27-013/2023MAEY27-013/2023MAEY27-013/2023MGérard Claude0a 0m 13j5H13450694Tx
15AEY27-014/2023FAEY27-014/2023FAEY27-014/2023FGérard Claude0a 0m 13j4B13450694Tx
16AEY27-015/2023FAEY27-015/2023FAEY27-015/2023FGérard Claude0a 0m 11j4B7450714T
17AEY27-016/2023FAEY27-016/2023FAEY27-016/2023FGérard Claude0a 1m 11j4B15450414T
18AEY27-017/2023MAEY27-017/2023MAEY27-017/2023MGérard Claude0a 1m 3j5H11450474T
19AEY27-018/2023MAEY27-018/2023MAEY27-018/2023MGérard Claude0a 0m 31j5H12450514Tx
20AEY27-019/2023FAEY27-019/2023FAEY27-019/2023FGérard Claude0a 0m 15j4B5450674T
21AEY27-020/2023MAEY27-020/2023MAEY27-020/2023MGérard Claude0a 0m 15j5H5450674Tx
22AEY27-021/2023FAEY27-021/2023FAEY27-021/2023FGérard Claude0a 0m 15j4B5450674T
23AEY27-022/2023MAEY27-022/2023MAEY27-022/2023MGérard Claude0a 0m 15j5H5450674T
24AEY27-023/2023MAEY27-023/2023MAEY27-023/2023MGérard Claude0a 0m 15j5H5450674Tx
25AEY27-024/2023MAEY27-024/2023MAEY27-024/2023MGérard Claude0a 0m 14j5H6450684Tx
Parents
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
harzer said:
I take the liberty of asking you for a fairly quick code because my data is important.
Click to expand...
Everyone's data is important.

See if this works for you:
VBA Code: 
Sub DeleteSpaces()
    Dim WS As Worksheet, rng As Range

    Set WS = ActiveSheet
    With WS
        Set rng = .Range("A1:C" & .Range("A" & .Rows.Count).End(xlUp).Row)
        rng.Replace What:=" ", Replacement:="", LookAt:=xlPart
    End With
End Sub
 
Upvote 0
Hello rlv01
Thank you for your reply.
The proposed code gives the desired results.
It's true that when I use your code in a test workbook that only has the test sheet, the code runs fast, but when I use it in real conditions with my data, the code takes a while to run and yet I reduced my data to 1100 rows.
I'm not an expert in coding but I think using arrays the execution will be faster.
Another quicker proposal would be welcome. thanks in advance.
Cheers
 
Upvote 0
I used 1000 rows and this macro and it took a fraction of a section.

Code: 
Sub nospace()
Dim lr, i As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
 For i = 2 To lr
 Cells(i, 1) = Replace(Cells(i, 1), " ", "")
 Cells(i, 2) = Replace(Cells(i, 2), " ", "")
 Cells(i, 3) = Replace(Cells(i, 3), " ", "")
 Next i
End Sub
 
Upvote 0
harzer said:
Hello rlv01
Thank you for your reply.
The proposed code gives the desired results.
It's true that when I use your code in a test workbook that only has the test sheet, the code runs fast, but when I use it in real conditions with my data, the code takes a while to run and yet I reduced my data to 1100 rows.
I'm not an expert in coding but I think using arrays the execution will be faster.
Another quicker proposal would be welcome. thanks in advance.
Cheers
Click to expand...
1. Yes, arrays might be somewhat faster, but you said you needed something quickly. Is your need not urgent?
2. Using your data as the pattern, I tested execution time and it runs in approximately 0.04 sec for ~1100 rows

VBA Code: 
Sub DeleteSpaces()
    Dim WS As Worksheet, rng As Range
    Dim ST As Single
   
    ST = Timer
    Set WS = ActiveSheet
    With WS
        Set rng = .Range("A2:C" & .Range("A" & .Rows.Count).End(xlUp).Row)
        rng.Replace What:=" ", Replacement:="", LookAt:=xlPart
    End With
   
    Debug.Print "Elapsed Time for " & rng.Rows.Count & " rows: " & Timer - ST
End Sub

1689381418026.png
 
Upvote 0
Solution
Hello rlv01 and kweaver,
I start by responding to Kweaver,
Thank you for your answer, I'm very sorry but your code in real test takes longer than RLV01.

Now I respond to RLV01,
I tried to understand why your code as well as that of Kweaver does not run quickly and I found an explanation, namely that there is a second code that snaps at: Private Sub Worksheet_Change

As a matter of fact, when I run your code, there is some code in Private Sub Worksheet_Change that also runs in the background, hence the slower processing.
To make sure that my information is correct, I disabled the code that is in: Private Sub Worksheet_Change and when running your code, I noticed that the processing is much smoother (the code executes better). THANKS.

Regarding your proposal, it's not urgent, I can wait for your proposal with arrays (if you still agree).

I thank you both for your respective proposals and congratulations to both of you for your availability.
Greetings to you both.
 
Upvote 0
I think that if you turn off events while the space-deleting macro runs, example:
VBA Code: 
Sub DeleteSpaces()
    Dim WS As Worksheet, rng As Range
    Dim ST As Single

    ST = Timer
    Set WS = ActiveSheet

    With WS
        Set rng = .Range("A2:C" & .Range("A" & .Rows.Count).End(xlUp).Row)
        Application.EnableEvents = False
        rng.Replace What:=" ", Replacement:="", LookAt:=xlPart
        Application.EnableEvents = True
    End With
    Debug.Print "Elapsed Time for " & rng.Rows.Count & " rows: " & Timer - ST
End Sub
there may be no need to pursue an array version.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
Insert the space character in the penultimate place of all the cells of columns "A" - "B" & "C".
Replies
3
Views
239
Fluff
Fluff
H
  • Solved
Modify content of cells in column "F" according to the content of cells in column "A"
Replies
5
Views
345
igold
igold
H
  • Solved
Grouping rows with the same values into columns "B" and "C"
Replies
5
Views
412
harzer
H
H
  • Solved
Count nonblank lines (between blank lines)
2
Replies
15
Views
704
Alex Blakenburg
A
H
  • Question
Copy rows from one sheet to another sheet whose name is given by the user.
Replies
4
Views
372
harzer
H

Forum statistics

Threads
1,215,360
Messages
6,124,489
Members
449,166
Latest member
hokjock

Share this page

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