Solved

Excel VB data cleaning script coding issue

Posted on 2015-02-04
12
107 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 40588552
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
ID: 40588561
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 30

Expert Comment

by:gowflow
ID: 40588590
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Rrave26
ID: 40588603
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 30

Expert Comment

by:gowflow
ID: 40588625
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
ID: 40588689
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
 
LVL 30

Expert Comment

by:gowflow
ID: 40588863
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 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 40588885
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
ID: 40589134
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 30

Expert Comment

by:gowflow
ID: 40589143
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
ID: 40589180
Solution worked like a charm.  Thanks for the help.
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40589232
Your welcome
gowflow
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

710 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