Solved

Excel VB data cleaning script coding issue

Posted on 2015-02-04
12
99 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
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 29

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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

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 29

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 29

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 29

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 29

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 29

Expert Comment

by:gowflow
ID: 40589232
Your welcome
gowflow
0

Featured Post

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

770 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