Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VB data cleaning script coding issue

Posted on 2015-02-04
12
Medium Priority
?
113 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 31

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 31

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 31

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 31

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 31

Accepted Solution

by:
gowflow earned 2000 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 31

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 31

Expert Comment

by:gowflow
ID: 40589232
Your welcome
gowflow
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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