Solved

Excel VB data cleaning script coding issue

Posted on 2015-02-04
12
96 Views
Last Modified: 2016-02-11
I have a worksheet, IM Raw Data, that has uneeded characters and spaces which affect my filtering and data pulling.  I have macro called datascrub that  attempted to run after about 5 mins I got a run time error and the line that is highlighted is this one:

    If Not c.Value = "" And c.HasFormula = False Then With c

What did I miss here?
Prouduction-IM-METRICS-T-V01.5.xlsm
0
Comment
Question by:Rrave26
  • 7
  • 5
12 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
What do you want that macro to do ? remove leading and trailing spaces ?

like
' Mr Joe blow  '
should be
'Mr Joe blow'

is that what you want ?
gowflow
0
 

Author Comment

by:Rrave26
Comment Utility
Hello GoFlow, Yes you are correct I want all leading and trailing spaces removed from the data.  Ironically you had help me build this script a couple of weeks ago.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
hein ? did I write this code ?? can you post what I built as this is taking extremely long and not impressed with this looping at all.

gowflow
0
 

Author Comment

by:Rrave26
Comment Utility
Oops, my bad, you didn't write this code.  Here is what I was given initially when I had asked for help:

Sub Macro1()
'
' Macro1 Macro
'
For Each c In ActiveSheet.UsedRange
    If Not c.Value = "" And c.HasFormula = False Then
        With c
            .Value = Replace(.Value, Chr(160), "")
            .Value = Application.WorksheetFunction.Clean(.Value)
            .Value = Trim(.Value)
        End With
    End If
Next c
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Anyway I think that
 .Value = Replace(.Value, Chr(160), "")

is an overkill as you have
 .Value = Application.WorksheetFunction.Clean(.Value)

Beside this to you have any reason to suspect that your data may have linefeed or non printable characters or carriage return that would handicap you filtering ? or it is only leading and trailing spaces ?

gowflow
0
 

Author Comment

by:Rrave26
Comment Utility
I am not sure if I have any linefeed or non printable characters or carriage returns.  I get this data from an outside party, how would I identify any of these issues that you have outlined?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Let me check one minute, anyway I made an other routine that should be speedier and safer !! you got the routine to stop as it encountered an error in a formula as despite you putting not has formula it evaluate the whole instruction and the instruction had <>"" so it halted.

Let me get back in 2 minutes.
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
ok here it is

Sub CleanData()
Dim WS As Worksheet
Dim cCol As Long
Dim cRow As Long
Dim Rng As Range, rCol As Range

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Set WS = Sheets("IM Raw Data")
MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
MaxCol = WS.Columns(WS.Columns.Count).End(xlToLeft).Column

For Each rCol In WS.UsedRange.EntireColumn
    If Application.WorksheetFunction.IsText(rCol.Cells.End(xlDown).Value) And _
     Not Application.WorksheetFunction.IsNumber(rCol.Cells.End(xlDown).Value) And _
     Not IsDate(rCol.Cells.End(xlDown).Value) And _
     Not rCol.Cells.End(xlDown).HasFormula Then
        '---> Create Formula in ZZ
        WS.Range("ZZ1:ZZ" & MaxRow).Formula = "=TRIM(" & Left(rCol.Address, InStr(1, rCol.Address, ":") - 1) & "1)"
        WS.Range("ZZ1:ZZ" & MaxRow).Copy
        WS.Range(Left(rCol.Address, InStr(1, rCol.Address, ":") - 1) & "1").PasteSpecial (xlPasteValues)
    End If
Next rCol

WS.Range("ZZ:ZZ").Delete

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

MsgBox "Data cleaned."

End Sub

Open in new window


Run this code on the worksheet and it should be fine.

Chk it is imbed in the workbook.
gowlfow
0
 

Author Comment

by:Rrave26
Comment Utility
Great I will.  When you mean imbed in the workbook you are referring to "This Workbook" under the Microsoft Excel Object VBA Project window correct?
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
no in Module3

I kept your original + scrub1 that I tried but preferred the one I posted CleanData

gowflow
0
 

Author Closing Comment

by:Rrave26
Comment Utility
Solution worked like a charm.  Thanks for the help.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Your welcome
gowflow
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now