Solved

Excel VB data cleaning script coding issue

Posted on 2015-02-04
12
103 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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
V-Lookup 11 20
Index Match Array? 5 39
Loop through records and update value in VBA 13 27
Decoding a line of VBA DataSort 11 10
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

828 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