-1

I'm trying to delete spaces in front of the text and after the text in a 20k records sheet. I tried different trim VBA code, but I'm not getting the result I want. Sometimes people copy in records into our sheets with lots of spaces in front of the text and after the text and sometimes it doesn't delete all spaces.

Does someone have an idea how I can do this?

Dadatrta
  • 7
  • 3
  • 2
    What you tried? It seems simple work. For big file it may take a time to complete operation but solution is simple. – Harun24hr Feb 07 '21 at 09:37
  • 1
    `Rng.Value = Application.Trim(Rng.Value)` where `Rng` is range variable. Please post your efforts. – shrivallabha.redij Feb 07 '21 at 10:25
  • 1
    Have a look at [this](https://stackoverflow.com/a/58454080/9758194) – JvdV Feb 07 '21 at 11:07
  • The TRIM function does exactly this. Work it into a loop that will go through each record. You might need to remove the file extension for each and add it back before the next cell. – Josh Feb 07 '21 at 12:16

1 Answers1

1

For speed read data into array Arr = Rng.Currentregion You can also use Arr = Rng.UsedRange -- I think Currentregion only select adjacent cells with values

Cyclizing through the array with for loop - copy of code recently used arr1(i, j) = Replace(arr1(i, j), " ", "") ' Removing blank spaces arr1(i, j) = Replace(arr1(i, j), Chr(10), "") ' Removing line breaks

Data can quickly be assigned back to sheet

Range = Arr -- Dont do it cell by cell it slows

Quicker to work with data in PC's memory than in the Excel Cells

MrT
  • 61
  • 4