Macro Trim 2 đầu (2 người xem)

[pomete]

Hi,

Nhờ mọi người viết hộ marco xóa bỏ những khoảng trống bao quanh dữ liệu ở hai đầu của một ô excel (hình như gọi là strim thì phải).
Marco này sẽ áp dụng cho tất cả các ô trong sheet đó.

Ví dụ tại ô A1 có dữ liệu như sau: " strim "
Marco sẽ có tác dụng xóa khoảng trống thành "strim" (dau " de chi bat dau va ket thuc cua mot o)

Thanks!

 

[hoangvuluan]

Có macro gì đâu bạn, Excel có hàm TRIM(text) dùng cho mục đích này mà!!!

 

[cadafi]

Hi,

Nhờ mọi người viết hộ marco xóa bỏ những khoảng trống bao quanh dữ liệu ở hai đầu của một ô excel (hình như gọi là strim thì phải).
Marco này sẽ áp dụng cho tất cả các ô trong sheet đó.

Ví dụ tại ô A1 có dữ liệu như sau: " strim "
Marco sẽ có tác dụng xóa khoảng trống thành "strim" (dau " de chi bat dau va ket thuc cua mot o)

Thanks!

Cái này đâu cần Macro gì đâu bạn, Dùng hàm TRIM(Text) của excel cũng được mà!
Còn nếu muốn xài Macro thì vẫn là Trim() thôi
[highlight=vb]
Sub MyTrim()
Dim MyCell As Range
For Each MyCell In Selection
MyCell.Value = Trim(MyCell.Value)
Next
End Sub
[/highlight]
Chọn vùng cần "TRIM", sau đó Ấn Alt+F8 để chọn macro này chạy, hoặc gán phím tắt cho macro để chạy!

 

[Po_Pikachu]

Em nghĩ code này sẽ chính xát hơn đúng không?

Sub chay() Dim MyCell As Range For Each MyCell In Cells.SpecialCells(2) MyCell.Value = Trim(MyCell.Value) Next End Sub

Thân.

 

[cadafi]

Em nghĩ code này sẽ chính xát hơn đúng không?

Sub chay()
Dim MyCell As Range
For Each MyCell In Cells.SpecialCells(2)
MyCell.Value = Trim(MyCell.Value)
Next
End Sub

Thân.

Mình quên cái vụ Ô có công thức thì không gán value!Cái vụ SpecialCells mà cứ quên hoài! Ẹc ẹc.....

Tham khảo thêm về SpecialCells tại đây!

 

[ndu96081631]

Bài này tôi có 1 cách không dùng For
Giã sử vùng dử liệu là A1:E20... Tôi đặt 1 name

Temp =TRIM($A$1:$E$20)

Rồi dùng code sau:

Sub RemoveAllSpaces()
  Range("A1:E20").Value = Evaluate("Temp")
End Sub

Hãy thử với dử liệu 20.000 dòng và so sánh về tốc độ nhé

 

[le tin]

Mình thử thấy các cell giống nhau cả

 

[Po_Pikachu]

Thì thực chất là vậy chứ sao? Vì A1 chỉ lấy dữ liệu từ G1 nên bất cứ thay đổi gì ở G1 điều làm cho giá trị xuất ra ở A1 bị thay đổi theo. Thế thôi! Thân.

 

[le tin]

Nhưng ở 2 sheet khác nhau lại không được thì phải

 

[cadafi]

Cách 1:
[highlight=vb]Sub MyTrim()
Dim MyCell As Range
For Each MyCell In Cells.SpecialCells(2)
MyCell.Value = Trim(MyCell.Value)
Next
End Sub[/highlight]

Cách 2:

Đặt 1 Name:

Temp =TRIM($A$1:$E$20)

Rồi dùng code sau:
[highlight=vb]
Sub RemoveAllSpaces()
Range("A1:E20").Value = Evaluate("Temp")
End Sub[/highlight]

Ưu điểm:
Cách 1: linh động trong vấn đề chọn vùng để thao tác
Cách 2: Xử lý nhanh đối với dữ liệu lớn.

Khuyết điểm:
Cách 1: Đối với dữ liệu lớn, tốc độ sẽ chậm, vì dùng For...Next quét từng ô
Cách 2: Không linh động trong vấn đề chọn vùng thao tác, vùng thao tác phải được định nghĩa trước.

 

[Po_Pikachu]

Hai sheet khác nhau thì lại là chuyện khác. Code này chỉ chạy trên sheet hiện hành thôi. Nếu dữ liệu ở 1 sheet khác thì cần chỉ rõ nguồn gốc của dữ liệu đó nằm ở đâu. Và lúc đó cần viết lại code như sau:

[COLOR=#000000][COLOR=#0000bb][FONT=Courier New]Sub chay[/FONT][/COLOR][FONT=Courier New][COLOR=#007700]() [/COLOR][COLOR=#0000bb]Dim MyCell [/COLOR][COLOR=#007700]As [/COLOR][/FONT][FONT=Courier New][COLOR=#0000bb]Range [/COLOR][COLOR=#007700]For [/COLOR][COLOR=#0000bb]Each MyCell In [COLOR=seagreen]Sheets("[/COLOR][COLOR=red]Ten sheet chua nguon[/COLOR][COLOR=seagreen]")[/COLOR].Cells[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]SpecialCells[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000bb]2[/COLOR][/FONT][FONT=Courier New][COLOR=#007700]) [/COLOR][COLOR=#0000bb]MyCell[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000bb]Trim[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000bb]MyCell[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]Value[/COLOR][/FONT][COLOR=#007700][FONT=Courier New]) [/FONT][/COLOR][COLOR=#0000bb][FONT=Courier New]Next End Sub [/FONT][/COLOR][/COLOR] [COLOR=#000000][COLOR=#0000bb][FONT=Courier New][COLOR=black]

[/COLOR][/FONT][/COLOR][/COLOR] Phần "Tên sheet nguồn" là tên của sheet chứa dữ liệu nguồn đó. Bạn thử lại xem. Thân.

 

[Po_Pikachu]

Ưu điểm: Cách 1: linh động trong vấn đề chọn vùng để thao tác Cách 2: Xử lý nhanh đối với dữ liệu lớn. Khuyết điểm: Cách 1: Đối với dữ liệu lớn, tốc độ sẽ chậm, vì dùng For...Next quét từng ô Cách 2: Không linh động trong vấn đề chọn vùng thao tác, vùng thao tác phải được định nghĩa trước.

Vậy hãy kết hợp chúng lại với nhau nha!

Sub MyTrim() Dim rng As Variant rng = Cells.SpecialCells(2).AddressLocal(ReferenceStyle:=xlR1C1) ActiveWorkbook.Names.Add Name:="Temp", RefersToR1C1:="=TRIM(" & ActiveSheet.Name & "!" & rng & ")" Cells.SpecialCells(2) = Evaluate("Temp") End Sub

Thân.

 

[ndu96081631]

Vậy hãy kết hợp chúng lại với nhau nha!

Sub MyTrim()
Dim rng As Variant
rng = Cells.SpecialCells(2).AddressLocal(ReferenceStyle:=xlR1C1)
ActiveWorkbook.Names.Add Name:="Temp", RefersToR1C1:="=TRIM(" & ActiveSheet.Name & "!" & rng & ")"
Cells.SpecialCells(2) = Evaluate("Temp")
End Sub

Thân.

Code này chưa chắc ăn đâu, vì vùng dử liệu có thể không liên tục ---> Chắc ăn hơn là nên làm 1 InputBox cho người dùng tùy chọn

 

[Po_Pikachu]

Vậy thì dùng code như vầy! Tưởng code trên khắc phục được mọi tình huống chứ, ai ngờ!!! Hiiii

Sub MyTrim() Dim rng As String Dim vung As Range On Error Resume Next Set vung = Application.InputBox(Prompt:="Chon vung", Type:=8) If vung Is Nothing Then Exit Sub rng = vung.AddressLocal(ReferenceStyle:=xlR1C1) ActiveWorkbook.Names.Add Name:="Temp", RefersToR1C1:="=TRIM(" & ActiveSheet.Name & "!" & rng & ")" vung = Evaluate("Temp") End Sub

Thân.

 

[hoangvuluan]

Có thể dùng thế này được không:

Sub sTrim()
Dim r As Range, c As Range, modCal
With Application
    modCal = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    On Error Resume Next
    Set r = Selection.SpecialCells(Type:=xlCellTypeConstants, Value:=xlTextValues)
    For Each c In r
        c.Value = .WorksheetFunction.Trim(c.Value)
    Next
    Set r = Nothing
    .Calculation = modCal
    .ScreenUpdating = True
End With
End Sub

Lưu ý: Trim của VBA khác với Trim của Excel

 

[Po_Pikachu]

Không biết có bác nào tìm được cách phân biệt nhiều vùng trong tập hợp vùng Cells.SpecialCells(2) không? Nếu tìm được thì chỉ cần dùng for cho từng vùng khác nhau. Điều này sẽ có ít nếu vùng trên Excel ít. @hoangvuluan: Code của bác nhìn dài nhưng nó vẫn là quy tắt For....Next từng ô thôi. Và Ưu điểm & Nhược điểm của cách này đã được bác Ca_Dafi nói ở trên rồi! Thân.

 

[ndu96081631]

Vậy thì dùng code như vầy! Tưởng code trên khắc phục được mọi tình huống chứ, ai ngờ!!! Hiiii

Sub MyTrim()
Dim rng As String
Dim vung As Range
On Error Resume Next
Set vung = Application.InputBox(Prompt:="Chon vung", Type:=8)
If vung Is Nothing Then Exit Sub
rng = vung.AddressLocal(ReferenceStyle:=xlR1C1)
ActiveWorkbook.Names.Add Name:="Temp", RefersToR1C1:="=TRIM(" & ActiveSheet.Name & "!" & rng & ")"
vung = Evaluate("Temp")
End Sub

Thân.

Cái này đâu có chạy được với vùng chọn không liên tục chứ
Còn cái này

Có thể dùng thế này được không:

Sub sTrim()
Dim r As Range, c As Range, modCal
With Application
    modCal = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    On Error Resume Next
    Set r = Selection.SpecialCells(Type:=xlCellTypeConstants, Value:=xlTextValues)
    For Each c In r
        c.Value = .WorksheetFunction.Trim(c.Value)
    Next
    Set r = Nothing
    .Calculation = modCal
    .ScreenUpdating = True
End With
End Sub

Lưu ý: Trim của VBA khác với Trim của Excel

Vẩn là For, không có gì đặc biệt về mặc cải thiện tốc độ cả
Tôi cải tiến như sau:

Sub RemoveAllSpaces()
 Dim i As Long
 On Error GoTo Thoat
 With Application.InputBox("Chon vung can xoa khoang trang", Type:=8).SpecialCells(2)
   For i = 1 To .Areas.Count
     .Areas(i).Value = Evaluate("=TRIM(" & .Areas(i).Address & ")")
   Next i
 End With
Thoat:  Exit Sub
End Sub

- Code cho phép chọn nhiều vùng không liên tục, khỏi cần đặt name
- Tuy là dùng For nhưng là quét qua các Areas chứ không phải quét qua các cell nên sẽ cho tốc độ cực nhanh
- Code vẩn có khả năng bỏ qua các cell chứa công thức
- Chỉ dùng 1 biến duy nhất
Hãy xem file đính kèm

 

[Po_Pikachu]

Lệnh Areas này là điều em còn thiếu đây! Cảm ơn bác nhiều nha! Thân.

 

[Po_Pikachu]

Mượn code của bác để bổ sung tính năng tự động luôn. Mục đích là khỏi cần phải quét vùng chi cho mệt đó mà! Hiii

Sub RemoveAllSpaces2()  Dim i As Long  On Error GoTo Thoat  With Cells.SpecialCells(2)    For i = 1 To .Areas.Count      .Areas(i).Value = Evaluate("=TRIM(" & .Areas(i).Address & ")")    Next i  End With Thoat:  Exit Sub End Sub

Thân.

 

[ndu96081631]

Mượn code của bác để bổ sung tính năng tự động luôn. Mục đích là khỏi cần phải quét vùng chi cho mệt đó mà! Hiii

Sub RemoveAllSpaces2()
 Dim i As Long
 On Error GoTo Thoat
 With Cells.SpecialCells(2)
   For i = 1 To .Areas.Count
     .Areas(i).Value = Evaluate("=TRIM(" & .Areas(i).Address & ")")
   Next i
 End With
Thoat:  Exit Sub
End Sub

Thân.

OK --- cách này cũng ổn... nhưng nếu trong 1 cell người ta cố tình muốn dử liệu có chứa vài khoảng trắng thì sao?
Vì thế InputBox tôi thấy vẩn thuận tiện hơn ---> Muốn chọn toàn bộ bảng tính này trong InputBox cũng đâu phải chuyện khó ---> Bấm vào giao điểm DÒNG - CỘT

 

[hoangvuluan]

Tại sao không dùng Selection thay cho Cells?

 

[pomete]

Hic, cảm ơn mọi người. Mình cũng biết lệnh TRIM nhưng nếu vậy thì phải tạo ra một cột nữa thì mới được nên mình sẽ phải dùng VBA. Nhiều đáp án quá, mình sẽ thử từng cách!

Thanks mọi người

 

[Po_Pikachu]

Tại sao không dùng Selection thay cho Cells?

Bác thích dùng cái nào thì dùng thôi, tùy hỉ!

Hic, cảm ơn mọi người. Mình cũng biết lệnh TRIM nhưng nếu vậy thì phải tạo ra một cột nữa thì mới được nên mình sẽ phải dùng VBA. Nhiều đáp án quá, mình sẽ thử từng cách! Thanks mọi người

Sao lại phải thử hết làm gì? Chỉ cần dùng 1 trong hai cách ở #17, #19 là được rồi! Thân.

 

[haonlh]

Còn nếu muốn xài Macro thì vẫn là Trim() thôi

Theo tôi, hàm TRIM của Excel xóa mọi kí tự trắng ở 2 đầu và thay mọi kí tự trắng liên tiếp
thành 1 kí tự trắng
Còn hàm TRIM của macro của Excel chỉ xóa mọi kí tự trắng ở 2 đầu thôi.

(tất nhiên người hỏi chỉ đặt xóa kí tự trắng ở 2 đầu, nếu thực thế thì phải làm macro)

 

[VuongTrungDuong]

Theo tôi, hàm TRIM của Excel xóa mọi kí tự trắng ở 2 đầu và thay mọi kí tự trắng liên tiếp thành 1 kí tự trắng
Còn hàm TRIM của macro của Excel chỉ xóa mọi kí tự trắng ở 2 đầu thôi.

(tất nhiên người hỏi chỉ đặt xóa kí tự trắng ở 2 đầu, nếu thực thế thì phải làm macro)

Vâng dỉ nhiên là như thế! Nhưng theo bạn thì có nhiều khoản trắng liên tiếp ở giửa các ký tự đễ làm gì chứ! Theo Tại hạ thì vẩn cứ TRIM là OK!

 

[ngocvtu]

Nhờ mọi người viết hộ marco xóa bỏ những khoảng trống bao quanh dữ liệu ở hai đầu của một ô excel (hình như gọi là strim thì phải).
Marco này sẽ áp dụng cho tất cả các ô trong cả file excel đó (nhiều sheet)

 

[Hoàng Trọng Nghĩa]

Nếu chỉ xóa 2 đầu thì dễ thôi!

Function Trim2End(ByVal txt As String) As String
    Trim2End = Trim(txt)
End Function

Cách dùng:

=Trim2End(A1)