How to make clean function more efficient?

Question

I have VBA running on an Excel sheet that translates the data in the sheet so I can import it into another application.

The below function cleans the data and removes text wrapping. This function takes a long time to run when the sheets have a large cell count. Since I am normalizing data to import to a relational database, there are frequently a lot of cells across the seven different sheets I have.

Is there a more efficient way to remove the text wrap and clean the data in the cells?

Dim ws As Worksheet
Dim x, lrow, lcol, active As Long
Dim r, cel As Range

active = ActiveWorkbook.Worksheets.count
For x = 1 To active
    Set ws = ThisWorkbook.Sheets(x)
    ws.Select
    Select Case ws.name
        Case "Solution", "Description", "Problem", "Buyer", "ProjectType", "Process", "Feature"
            lrow = ws.UsedRange.Rows(ActiveSheet.UsedRange.Rows.count).row
            lcol = ws.UsedRange.Columns(ActiveSheet.UsedRange.Rows.count).Column
            If lrow > 1 Then
                Set r = ws.Range(Cells(2, 1), Cells(lrow, lcol))
                For Each cel In r.Cells
                    cel.WrapText = False
                    cel.Value = Application.WorksheetFunction.Clean(cel.Value)
                Next cel
            End If
        Case Else
    End Select
    ws.Cells(1, 1).Select

ThisWorkbook.Sheets("Solution").Activate
Next x

you can [start here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Also, if you code works and you just want to optimize it, then the question is more suited to https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — cybernetic.nomad, Feb 03 '20 at 22:50

chris neilsen · Accepted Answer · 2020-02-09T08:09:13.610

2

Your code can be reduced to

Sub Demo()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Select Case ws.Name
            Case "Solution", "Description", "Problem", "Buyer", "ProjectType", "Process", "Feature"
                With ws.UsedRange
                    .WrapText = False
                    .Value = ws.Evaluate("Clean(" & .Address & ")")
                End With
        End Select
    Next
End Sub

On my hardware, a sheet with 100,000 rows 26 columns ran in about 6s

Note: OPs claim (in comment) that "it changes the value of every cell on the sheet to the first value in the first sheet it encounters." - tested this claim and it's not accurate. This code works as advertised.

edited Feb 09 '20 at 08:09

answered Feb 03 '20 at 23:06

chris neilsen

52,446
10
84
123

Thanks Chris, question for you on this (I am a learning as I go on this so apologies if the question is ignorant). When I run your code, it changes the value of every cell on the sheet to the first value in the first sheet it encounters. In this case, the value in cell A1 of the Solution sheet is copied to all cells in the UsedRange of each sheet. How do I get it to actually look at the value in each cell instead? – Nick Osborne Feb 04 '20 at 15:13
I did a little digging and found that if I change the statement to .value = Evaluate("index(clean(" & ws.UsedRange.Address & "),)") I get the results I need. Thanks – Nick Osborne Feb 04 '20 at 15:30

score 0 · Answer 2 · answered Feb 03 '20 at 22:53

To remove the text wrapping property (and it surprises me that has an affect on your external program), you should be able to do just:

r.WrapText = False

For the Clean, what, exactly, is in the cells that you are trying to remove?

It would be faster to read the cells into an array; process them, and write them back.

Something like: (not debugged)

Dim V, I as long, J as Long
v = R

for i = 1 to ubound(v)
  for j = 1 to ubound(v,2)
     `worksheetfunction.clean(v(i,j))
     `or some other function to remove unwanted characters
  next j
next i

r.clear
r.value = v

You should also be aware that UsedRange is not particularly reliable and may wind up with you processing many more cells than necessary.

There are a number of posts on this forum showing better methods of determing the Last Row and Last Column.

The source text is copied from a couple different sources into the sheet. It has various non-printing characters depending on the source. Some are new line, some are carriage return, etc. The Clean function works great for what I need, my method just seems to be slow. — Nick Osborne, Feb 04 '20 at 14:50
If `Clean` does everything you need, then either mine or @chrisneilson solution should work for you. — Ron Rosenfeld, Feb 04 '20 at 18:31

How to make clean function more efficient?

2 Answers2