0

I need to clean extracted ERP data from many sheets so I can analyse it.

An example of the data format:

   
Material MvTS   Mat. Doc.   Item    Pstng Date  Time

1091769             Air Mover Type AM20-SN      
3   *711        4903961381      25.10.2018  11:29:03

3 *711      4903961381      25.10.2018  11:29:03

1598718             Filter      
3   711     4901254258      12.04.2018  10:52:06

1652333             O-Ring FKM 37.82x1.78 Black     
3   711     4904214978      14.11.2018  09:53:54

1653970             Gasket assembly EV344D 20 PA        
3   711     4903943685      24.10.2018  13:18:48

I want to:

  1. Use inputbox to determine the range of the data needing to be cleansed
  2. If Material = 3 replace with the material number in the cell above
  3. If MVt=="" (Empty) delete the row
Sub CleanVendorData()

    Dim r As Range
    Dim row As Long
    Dim col As Long
    Dim adr As Range
    Dim adr1 As Range
    Dim HeadRow As Double
    Dim LowRow As Double
    Dim num As Double
    Dim i As Double
    Dim dynamic As Long

    Dim rng As Range
    Set rng = Application.InputBox("Choose a random cell in the Vendor file that you want to clean", "Clean Vendor forecast file", Type:=8)
    Workbooks(rng.Worksheet.Parent.Name).Activate

    'Deleting empty rows.
    Range("A100000").Select
    Selection.End(xlUp).Select
    Set adr1 = ActiveCell
    LowRow = adr1.row

    'finding out how bit the area of data is before its deleted:)
    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Set adr = ActiveCell
    dynamic = adr.row

    Set r = ActiveSheet.Range(Cells(dynamic, 1), Cells(LowRow + dynamic, 20))
    '"A10:s50000")
    row = r.Rows.Count
    For i = row To 1 Step (-1)
        If WorksheetFunction.CountA(r.Rows(i)) = 0 Then r.Rows(i).Delete
        If WorksheetFunction.CountA(r.Rows(i)) = 1 Then r.Rows(i).Delete
    Next

    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Set adr = ActiveCell
    HeadRow = adr.row
    Rows(HeadRow).Clear
    num = Cells(HeadRow + 1, 1).CurrentRegion.Rows.Count - 2

    Next

    MsgBox ("Done")

End Sub
Community
  • 1
  • 1
mat.andersen
  • 1
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Apr 26 '19 at 09:19
  • What exactly does "refine this code" mean? Does it work? How about posting before and after screenshots? – SJR Apr 26 '19 at 09:37
  • 1 too many `Next` in there will prevent this from even compiling. After that, if your definition of *refine* is make better, you've got variables with same name as properties (eg. row), unqualified ranges being used, row numbers stored in `Double` rather than `Long`, nothing to capture/deal with if no `rng` is set, range/sheet activation / selection.. we could probably go on but fixing those should put you in a much better place. – CLR Apr 26 '19 at 10:09

0 Answers0