When import worksheets, How can I use Trim function in excel 2010 vba I import d

Question

When import worksheets, How can I use Trim function in excel 2010 vba

I import data from different workbook.

In the source workbook, there are city names.

Some people enters space at the beginning, or ending in a city name cell, then that cause an error.

I can't use sumifs function.

When I import, how can i prevent these errors?

I copy colomn A to M from source workbook. I am glad if I can solve this problem at the very begining.

Or that is not possible, then when I use sumifs function in vba, how can I use trim function?

The city name range is column C. I don't know how to clean these spaces before using column C as range for sumifs.

.Range("Q" & L) = Application.WorksheetFunction.SumIfs(WorkHourRange, _
           NameRange, .Range("P" & L).Value, TaskCodeRange, .Range("Q5").Value, CityNameRange, .Range("P5")

Range("P5").value is not the same in column C. Some citys are correct and some citys are not.

Any suggestions???

Thanks !!

Explanation / Answer

Hi

If the town names are in column C, to remove initiall or trailing spaces, try something like:

'=========>>
Option Explicit

'--------->>
Public Sub Tester()
Dim WB As Workbookk
Dim SH As Worksheet
Dim Rng As Range
Dim Arr As Variant
Dim LRow As Long, i As Long

Set WB = ActiveWorkbook
Set SH = WB.Sheets("Sheet1") '<<===== Change

With SH
LRow = .Cells(Rows.Count, "C").End(xlUp).Row
Set Rng = .Range("C1:C" & LRow)
End With

Arr = Rng.Value

For i = 1 To LRow
Arr(i, 1) = Trim(Arr(i, 1))
Next i

Rng.Value = Arr
End Sub

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.