Vba excel trim не работает
I have some applications that contain userforms which were originally created for use with Excel 2003 but have subsequently migrated to Excel 2010 on a Windows 7 platform saved as .xlsm. The applications work successfully on both my computer and those of my colleagues.
I have recently created a new application that uses much of the code from the earlier applications but now the new application works on my computer but none of my colleagues. All computers have Excel 2010 and Windows 7.
The initial problem that cause the first error was the use of 'Date'. This error was overcome by using 'Now' instead of 'Date'.
As one problem is solved another comes to light. The use of Trim caused the next error. 'Application.Trim' appears to solve the problem.
The next error to come to light is the use of 'UCase'. As one error is solved another comes to light. Where will it end.
Knowing the errors which are occurring on one computer and not on the other, what could the differences in set-up between the two computers be?
Is the use of 'Application.' where each error occurs be the cure, but why is this now needed in the script when it was not before.
This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread.
Report abuse
Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.
It is unable to remove the spaces in between the text.
I need to remove the extra spaces so I found Trim to do it but it is not working while ltrim and rtrim are.
Replace(Activesheet.cells(i,"C").value," ") would get rid of all spaces in the string. Not sure if that's want you want though .
Not really that important but you can remove the activesheet portion. And a for each loop through range("C3:C2000") would probobly be faster.
10 Answers 10
Excel Trim will remove all spaces except a single space between words. VBA Trim will remove leading and trailing spaces.
Thank MS for using the same keyword for different functions.
Trim removes extra spaces at start and end, not in the middle of a string.
PS. It's not the most efficient way to remove spaces. I wouldn't use on many, very long strings or in a tight loop. It might be suitable for your situation.
As an aside, from time to time, the seeming spaces in Excel are not the usual character ASCII 32, but some other character, ASCII 255, for example. In these cases, trim and replace " " will not work.
Would a regex looking for multiple spaces and replacing it with a single one would be more efficient? (just suggesting a more performing way if you think that could work)
see my answer. It's as simple as using Excel's function rather than VBAs version (they produce different results).
I know this question is old but I just found it and thought I'd add what I use to remove multiple spaces in VBA.
Thanks I could not accept your answer but i will upvote it for finding something for me Thanks again !
When you call Trim() VBA is actually calling Strings.Trim(). This function will only remove leading and trailing spaces. To remove excessive spaces within a string, use
Are all your other functions leaving whitespace behind?
CleanUltra removes all whitespace and non-printable characters including whitespace left behind by other functions!
I hope you find this useful. Any improvements are welcome!
Here's an example of it's usage:
Here's a test I ran to verify that the function actually removed all whitespace. vbNullChar was particularly devious. I had to set the function to remove it first, before the CLEAN and TRIM functions were used to stop them from removing all characters after the vbNullChar .
My related issue was that the last character was a chr(160) - a non-breaking space. So trim(replace(Str,chr(160),"")) was the solution.
I know this question is old but I just want to share my solution on how to deal and fix with this issue.
Maybe you might wondering why sometimes TRIM function isn't working, remember that it will only remove spaces and spaces are equivalent to ASCII 32. So if these ASCII 13 or ASCII 10 exists in the Beginning or end of your string value then TRIM function will not work on it.
With this code it works for me, by the way if this might not work on your side then try to check the ASCII of you string value because it might have another invisible special char that might not covered on my code to replace on it, kindly add on it to work.
Please see reference for some invisible special char.
Привет. В одной книге макрос выполняется всегда, а в другой он же выполняется всегда с ошибкой в конце!! Ошибка начинается после того как макрос обработает примерно 20 или 30 строк! Ошибка заключается в том что макрос с середины начинает копировать значения,, которые состоят менее чем из 15 символов. Почему??
[vba]
bla bla bla
If Len(Trim(Cells(Y, X))) > 15 Then
Cells(Z, 17) = Cells(Y, X)
Z = Z + 1
End If
Next
bla bla bla
Привет. В одной книге макрос выполняется всегда, а в другой он же выполняется всегда с ошибкой в конце!! Ошибка начинается после того как макрос обработает примерно 20 или 30 строк! Ошибка заключается в том что макрос с середины начинает копировать значения,, которые состоят менее чем из 15 символов. Почему??
[vba]
bla bla bla
If Len(Trim(Cells(Y, X))) > 15 Then
Cells(Z, 17) = Cells(Y, X)
Z = Z + 1
End If
Next
bla bla bla
bla bla bla
If Len(Trim(Cells(Y, X))) > 15 Then
Cells(Z, 17) = Cells(Y, X)
Z = Z + 1
End If
Next
bla bla bla
[/vba]
[moder]Покажите эту неправильную книгу и весь код макроса Автор - Tina90
Дата добавления - 06.04.2016 в 15:43
вероятно потому, что Trim убирает пробелы слева и справа, в середине оставляет.
Если мое предположение верно, то нужно использовать WorksheetFunction.Trim
вероятно потому, что Trim убирает пробелы слева и справа, в середине оставляет.
Если мое предположение верно, то нужно использовать WorksheetFunction.Trim Michael_S
А еще бывает, что пробелы на поверку - вовсе и не пробелы. вернее совсем не те пробелы, которые убирает Trim.
А еще бывает, что пробелы на поверку - вовсе и не пробелы. вернее совсем не те пробелы, которые убирает Trim. Michael_S
to Moderator ► к сожалению книгу с ошибками показать не могу (государственная тайна, или военная уже не помню!!). весь код макроса вот ►
[vba]
Dim X As Integer, Y As Integer, Z As Integer
On Error Resume Next
Z = 1
For Y = 1 To 96
For X = 10 To 10
If Len(Trim(Cells(Y, X))) > 39 Then
Cells(Z, 1) = Cells(Y, X)
End If
Next
Next
Тогда мне странно вдвойне! Так как же тогда , если в двух разных книгах один и тот же макрос правильный\неправильный?? Через воркшит нельзя, макрос запускается кнопкой а не по факту события!
to Moderator ► к сожалению книгу с ошибками показать не могу (государственная тайна, или военная уже не помню!!). весь код макроса вот ►
[vba]
Dim X As Integer, Y As Integer, Z As Integer
On Error Resume Next
Z = 1
For Y = 1 To 96
For X = 10 To 10
If Len(Trim(Cells(Y, X))) > 39 Then
Cells(Z, 1) = Cells(Y, X)
End If
Next
Next
Тогда мне странно вдвойне! Так как же тогда , если в двух разных книгах один и тот же макрос правильный\неправильный?? Через воркшит нельзя, макрос запускается кнопкой а не по факту события! Tina90
Dim X As Integer, Y As Integer, Z As Integer
On Error Resume Next
Z = 1
For Y = 1 To 96
For X = 10 To 10
If Len(Trim(Cells(Y, X))) > 39 Then
Cells(Z, 1) = Cells(Y, X)
End If
Next
Next
Тогда мне странно вдвойне! Так как же тогда , если в двух разных книгах один и тот же макрос правильный\неправильный?? Через воркшит нельзя, макрос запускается кнопкой а не по факту события! Автор - Tina90
Дата добавления - 06.04.2016 в 19:23
может я и дура, но не настолько же!! только случаи 13 и 18 вы сами перепутали, Семен Семеныч!! Tina90
может я и дура, но не настолько же!! только случаи 13 и 18 вы сами перепутали, Семен Семеныч!! Автор - Tina90
Дата добавления - 06.04.2016 в 19:26
Может попробовать явно указать к чему относятся все эти Cells(. ). В смысле родительский объект.
[vba]
Sub TrimmText()
Dim X As Integer, Y As Integer, Z As Integer
On Error Resume Next
with activesheet
Z = 1
For Y = 1 To 96
If Len(Trim(.Cells(Y, 10))) > 39 Then
.Cells(Z, 1) = .Cells(Y, 10)
Z = Z + 1
End If
Next
end with
End Sub
Может попробовать явно указать к чему относятся все эти Cells(. ). В смысле родительский объект.
[vba]
Sub TrimmText()
Dim X As Integer, Y As Integer, Z As Integer
On Error Resume Next
with activesheet
Z = 1
For Y = 1 To 96
If Len(Trim(.Cells(Y, 10))) > 39 Then
.Cells(Z, 1) = .Cells(Y, 10)
Z = Z + 1
End If
Next
end with
End Sub
Sub TrimmText()
Dim X As Integer, Y As Integer, Z As Integer
On Error Resume Next
with activesheet
Z = 1
For Y = 1 To 96
If Len(Trim(.Cells(Y, 10))) > 39 Then
.Cells(Z, 1) = .Cells(Y, 10)
Z = Z + 1
End If
Next
end with
End Sub
Всем спасибо!! У меня в книге была ошибка, результат события листа оставлял данные в том массиве, в который проводилось копирование! Теперь тот массив перед копированием очищаю и все в порядке. а ведь уже собиралась гневное письмо писать в майкрософт
Всем спасибо!! У меня в книге была ошибка, результат события листа оставлял данные в том массиве, в который проводилось копирование! Теперь тот массив перед копированием очищаю и все в порядке. а ведь уже собиралась гневное письмо писать в майкрософт Tina90
Информация о сайте
Инструменты и настройки
Excel Windows
и
Excel Macintosh
Вопросы и решения
Работа и общение
Работа форума и сайта
Функции листа Excel
= Мир MS Excel/Справочники
Все функции VBA [3] |
Математические функции [14] |
Функции обработки массивов [4] |
Функции обработки строк [27] |
Функции преобразования типа данных [11] |
Функции загрузки данных [8] |
Функции работы с файлами [12] |
Функции обработки системных параметров [16] |
Функции обработки цвета [2] |
Функции работы с датами и временем [21] |
Функции преобразования чисел в разные системы счисления [3] |
Функции работы с объектами [4] |
Финансовые функции [13] |
Функции форматирования [5] |
Функции работы с указателями [3] |
Функция Trim
Функция Trim объединяет действия двух функций LTrim и RTrim, удаляя пробелы как справа, так и слева
Возвращаемое значение
Возвращает исходную строку типа Variant(String) без начальных и завершающих пробелов. Если аргумент имеет значение Null , то возвращается Null
Параметры String Обязательный аргумент может представлять любое строковое выражение
Макрос Trim_By_Formula применяет функцию листа СЖПРОБЕЛЫ к ячейкам выделенного диапазона.
В результате из текстовых значений ячеек удаляются все лидирующие, финиширующие пробелы.
Каждый из многократно повторяющихся пробелов внутри текста ячейки заменяется на единичный пробел.
Скрытые (невидимые) ячейки и ячейки, содержащие формулы, дату и время макросом игнорируются.
[vba]
Sub Trim_By_Formula() ' применить функцию СЖПРОБЕЛЫ к ячейкам выделенного диапазона
If TypeName(Selection) <> "Range" Then Exit Sub
Dim iCell As Range, rRange As Range
With ActiveSheet.Cells
Set rRange = Intersect(Selection, ActiveSheet.UsedRange, .SpecialCells(xlCellTypeVisible), .SpecialCells(xlCellTypeConstants))
End With
If rRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each iCell In rRange
With iCell
If Not IsDate(.Value) And Not .NumberFormat Like "*" & ":" & "*" Then 'если в ячейке не дата и не время
.Value = Application.WorksheetFunction.Trim(.Value)
End If
End With
Next
Application.ScreenUpdating = True
rRange.Select
End Sub
Макрос Trim_By_Formula применяет функцию листа СЖПРОБЕЛЫ к ячейкам выделенного диапазона.
В результате из текстовых значений ячеек удаляются все лидирующие, финиширующие пробелы.
Каждый из многократно повторяющихся пробелов внутри текста ячейки заменяется на единичный пробел.
Скрытые (невидимые) ячейки и ячейки, содержащие формулы, дату и время макросом игнорируются.
[vba]
Sub Trim_By_Formula() ' применить функцию СЖПРОБЕЛЫ к ячейкам выделенного диапазона
If TypeName(Selection) <> "Range" Then Exit Sub
Dim iCell As Range, rRange As Range
With ActiveSheet.Cells
Set rRange = Intersect(Selection, ActiveSheet.UsedRange, .SpecialCells(xlCellTypeVisible), .SpecialCells(xlCellTypeConstants))
End With
If rRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each iCell In rRange
With iCell
If Not IsDate(.Value) And Not .NumberFormat Like "*" & ":" & "*" Then 'если в ячейке не дата и не время
.Value = Application.WorksheetFunction.Trim(.Value)
End If
End With
Next
Application.ScreenUpdating = True
rRange.Select
End Sub
Sub Trim_By_Formula() ' применить функцию СЖПРОБЕЛЫ к ячейкам выделенного диапазона
If TypeName(Selection) <> "Range" Then Exit Sub
Dim iCell As Range, rRange As Range
With ActiveSheet.Cells
Set rRange = Intersect(Selection, ActiveSheet.UsedRange, .SpecialCells(xlCellTypeVisible), .SpecialCells(xlCellTypeConstants))
End With
If rRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each iCell In rRange
With iCell
If Not IsDate(.Value) And Not .NumberFormat Like "*" & ":" & "*" Then 'если в ячейке не дата и не время
.Value = Application.WorksheetFunction.Trim(.Value)
End If
End With
Next
Application.ScreenUpdating = True
rRange.Select
End Sub
Леш, как-то я не обращал внимания на тему. Когда-то на форуме(не помню где) я предложил такую конструкцию:
[vba]
[/vba]
Она делала для темы все как надо, однако твой тезка Казанский резонно предположил, что SpecialCells(2) может состоять из нескольких диапазонов. Тогда моя конструкция просто запортит данные. И предложил свой вариант:
[vba]
[/vba]
Так гораздо быстрей, чем если перебирать по одной ячейке. Пример прилагаю.
PS А зачем ты пропускал даты и время? Я в пример специально в А1 поставил дату-время.
Леш, как-то я не обращал внимания на тему. Когда-то на форуме(не помню где) я предложил такую конструкцию:
[vba]
[/vba]
Она делала для темы все как надо, однако твой тезка Казанский резонно предположил, что SpecialCells(2) может состоять из нескольких диапазонов. Тогда моя конструкция просто запортит данные. И предложил свой вариант:
[vba]
[/vba]
Так гораздо быстрей, чем если перебирать по одной ячейке. Пример прилагаю.
PS А зачем ты пропускал даты и время? Я в пример специально в А1 поставил дату-время. KuklP
[/vba]
Она делала для темы все как надо, однако твой тезка Казанский резонно предположил, что SpecialCells(2) может состоять из нескольких диапазонов. Тогда моя конструкция просто запортит данные. И предложил свой вариант:
[vba]
[/vba]
Так гораздо быстрей, чем если перебирать по одной ячейке. Пример прилагаю.
PS А зачем ты пропускал даты и время? Я в пример специально в А1 поставил дату-время. Автор - KuklP
Дата добавления - 12.08.2012 в 20:21
Серёга, ты обратил внимание, когда пост был мною написан? 2 года назад. Глупый был, не опытный
Сейчас макрос выглядит несколько по-другому:
[vba]
Sub Trim_By_Formula() ' применить функцию СЖПРОБЕЛЫ к видимым ячейкам выделенного диапазона
If TypeName(Selection) <> "Range" Then Exit Sub
Dim rCell As Range, rRange As Range
With ActiveSheet.Cells
Set rRange = Intersect(Selection, ActiveSheet.UsedRange, .SpecialCells(xlCellTypeVisible), .SpecialCells(xlCellTypeConstants))
End With
If rRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each rCell In rRange
With rCell
.Value = Replace(.Value, Chr(160), " ") ' заменить неразрывный пробел Chr(160) на простой
.Value = Application.WorksheetFunction.Trim(.Value) ' СЖПРОБЕЛЫ
.Value = Replace(.Value, " " & Chr(10), Chr(10)) ' убрать пробел перед LF
.Value = Replace(.Value, Chr(10) & " ", Chr(10)) ' убрать пробел после LF
End With
Next rCell
Application.ScreenUpdating = True
rRange.Select
End Sub
Серёга, ты обратил внимание, когда пост был мною написан? 2 года назад. Глупый был, не опытный
Сейчас макрос выглядит несколько по-другому:
[vba]
Sub Trim_By_Formula() ' применить функцию СЖПРОБЕЛЫ к видимым ячейкам выделенного диапазона
If TypeName(Selection) <> "Range" Then Exit Sub
Dim rCell As Range, rRange As Range
With ActiveSheet.Cells
Set rRange = Intersect(Selection, ActiveSheet.UsedRange, .SpecialCells(xlCellTypeVisible), .SpecialCells(xlCellTypeConstants))
End With
If rRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each rCell In rRange
With rCell
.Value = Replace(.Value, Chr(160), " ") ' заменить неразрывный пробел Chr(160) на простой
.Value = Application.WorksheetFunction.Trim(.Value) ' СЖПРОБЕЛЫ
.Value = Replace(.Value, " " & Chr(10), Chr(10)) ' убрать пробел перед LF
.Value = Replace(.Value, Chr(10) & " ", Chr(10)) ' убрать пробел после LF
End With
Next rCell
Application.ScreenUpdating = True
rRange.Select
End Sub
Серёга, ты обратил внимание, когда пост был мною написан? 2 года назад. Глупый был, не опытный
Сейчас макрос выглядит несколько по-другому:
[vba]
Sub Trim_By_Formula() ' применить функцию СЖПРОБЕЛЫ к видимым ячейкам выделенного диапазона
If TypeName(Selection) <> "Range" Then Exit Sub
Dim rCell As Range, rRange As Range
With ActiveSheet.Cells
Set rRange = Intersect(Selection, ActiveSheet.UsedRange, .SpecialCells(xlCellTypeVisible), .SpecialCells(xlCellTypeConstants))
End With
If rRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each rCell In rRange
With rCell
.Value = Replace(.Value, Chr(160), " ") ' заменить неразрывный пробел Chr(160) на простой
.Value = Application.WorksheetFunction.Trim(.Value) ' СЖПРОБЕЛЫ
.Value = Replace(.Value, " " & Chr(10), Chr(10)) ' убрать пробел перед LF
.Value = Replace(.Value, Chr(10) & " ", Chr(10)) ' убрать пробел после LF
End With
Next rCell
Application.ScreenUpdating = True
rRange.Select
End Sub
[/vba]
я сам про этот топик уже забыл давно, поэтому и не подправил Автор - Alex_ST
Дата добавления - 14.08.2012 в 23:04
Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина - самый громкий звук
YM 41001156540584 / WM WMR R21924176233
Ну и что ты этим хотел сказать?
ИМХО, это монопенисуально .
Разница ровно в 1 символ по длине кода. TypeName и TypeOf я применяю рэндомно (какой первым в голову придёт). А на счёт скорости у меня данных нет.
К стати, у меня на рабочем компе несколько сокращённый вариант:
[vba]
Sub Trim_By_Formula() ' применить функцию СЖПРОБЕЛЫ к видимым ячейкам выделенного диапазона
If TypeName(Selection) <> "Range" Then Exit Sub
Dim rCell As Range, rRange As Range
With ActiveSheet.UsedRange
Set rRange = Intersect(Selection, .SpecialCells(xlCellTypeVisible), .SpecialCells(xlCellTypeConstants))
End With
If rRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each rCell In rRange
With rCell
.Value = Replace(.Value, Chr(160), " ") ' Chr(160) - неразрывный пробел
.Value = Application.WorksheetFunction.Trim(.Value) ' СЖПРОБЕЛЫ
.Value = Replace(.Value, " " & Chr(10), Chr(10)) ' пробел перед LF
.Value = Replace(.Value, Chr(10) & " ", Chr(10)) ' пробел после LF
End With
Next rCell
Application.ScreenUpdating = True
rRange.Select
End Sub
[/vba]
Вот если бы ты предложил RegExp вместо Replace и Application.Trim, то, наверное, было бы красиво.
Я, к сожалению, RegExp так толком и не осилил. А ковыряться в чужих не совсем подходящих и переделывать их лень.
Ну и что ты этим хотел сказать?
ИМХО, это монопенисуально .
Разница ровно в 1 символ по длине кода. TypeName и TypeOf я применяю рэндомно (какой первым в голову придёт). А на счёт скорости у меня данных нет.
К стати, у меня на рабочем компе несколько сокращённый вариант:
[vba]
Sub Trim_By_Formula() ' применить функцию СЖПРОБЕЛЫ к видимым ячейкам выделенного диапазона
If TypeName(Selection) <> "Range" Then Exit Sub
Dim rCell As Range, rRange As Range
With ActiveSheet.UsedRange
Set rRange = Intersect(Selection, .SpecialCells(xlCellTypeVisible), .SpecialCells(xlCellTypeConstants))
End With
If rRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each rCell In rRange
With rCell
.Value = Replace(.Value, Chr(160), " ") ' Chr(160) - неразрывный пробел
.Value = Application.WorksheetFunction.Trim(.Value) ' СЖПРОБЕЛЫ
.Value = Replace(.Value, " " & Chr(10), Chr(10)) ' пробел перед LF
.Value = Replace(.Value, Chr(10) & " ", Chr(10)) ' пробел после LF
End With
Next rCell
Application.ScreenUpdating = True
rRange.Select
End Sub
[/vba]
Вот если бы ты предложил RegExp вместо Replace и Application.Trim, то, наверное, было бы красиво.
Я, к сожалению, RegExp так толком и не осилил. А ковыряться в чужих не совсем подходящих и переделывать их лень. Alex_ST
Sub Trim_By_Formula() ' применить функцию СЖПРОБЕЛЫ к видимым ячейкам выделенного диапазона
If TypeName(Selection) <> "Range" Then Exit Sub
Dim rCell As Range, rRange As Range
With ActiveSheet.UsedRange
Set rRange = Intersect(Selection, .SpecialCells(xlCellTypeVisible), .SpecialCells(xlCellTypeConstants))
End With
If rRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each rCell In rRange
With rCell
.Value = Replace(.Value, Chr(160), " ") ' Chr(160) - неразрывный пробел
.Value = Application.WorksheetFunction.Trim(.Value) ' СЖПРОБЕЛЫ
.Value = Replace(.Value, " " & Chr(10), Chr(10)) ' пробел перед LF
.Value = Replace(.Value, Chr(10) & " ", Chr(10)) ' пробел после LF
End With
Next rCell
Application.ScreenUpdating = True
rRange.Select
End Sub
[/vba]
Вот если бы ты предложил RegExp вместо Replace и Application.Trim, то, наверное, было бы красиво.
Я, к сожалению, RegExp так толком и не осилил. А ковыряться в чужих не совсем подходящих и переделывать их лень. Автор - Alex_ST
Дата добавления - 15.08.2012 в 08:47
Читайте также: