• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formatting Unstructured Data in Excel VBA

C

coolkiran

Member
Hi All

I am getting unstructured data from one application. I need to delete blank rows and delete column. I have attached my input file and how i need output file.

Can anyone assist me on this.
 

Attachments

  • CH1.xlsm
    21.4 KB · Views: 12
coolkiran
As written:
You should send sample of RAW-data here.
How did You import that data to Your file? .. copy & paste?
or
Did that one application saved it as CH1.xlsm?
or
How?
 
vletm said:
coolkiran
As written:
You should send sample of RAW-data here.
How did You import that data to Your file? .. copy & paste?
or
Did that one application saved it as CH1.xlsm?
or
How?
Click to expand...

Hi

Raw data is in Input sheet. I will copy paste raw data to Input sheet. From here onwards macro or Excel formulas to automate.
 
... hmm? ...
Could You send that RAW-data file here?
Copy and Paste makes always challenges!
 
Hi

I am confused, i already attached the raw file in my original post. (Input sheet is my raw file, that i am getting from one software).

I want to delete blank rows and columns and final out put like in sheet Output.

Please let me know.
 
Last edited by a moderator:
As You wrote ... I (You) will copy paste the raw file ... #5 Reply ...
If someone Copy and Paste something 'normal way'
It would make a lot of extra work to clean!
In many cases other ways to 'import' data to Excel gives more useful result!
>> Less work for You and less much less work for formatting! <<
If You won't upload real raw file then
maybe someone else would do some extra work?
 
Hi

Here is the raw file that will get from one application. It will be in .xlsx format. I will copy data from CH sheet and paste to my macro file to run and generate output in Output sheet.
 

Attachments

  • CH1.xlsx
    11 KB · Views: 12
coolkiran
I have tried to be positive ...
Do You really mean that
one application gives CH1.xlsx and
Screen Shot 2018-03-04 at 13.48.20.png
after Your Copy Paste You have gotten CH1.xlsm?
Screen Shot 2018-03-04 at 13.46.13.png
If so then something not match!
 
Hi Vletm

Ok, Here is the scenario.

I have SAP application, From that i will get raw data which will be in .xlsx format. From that i cannot insert macro, so i have created new Excel macro enabled file. there will be 2 sheets one is Input and second one is Output.

So, what i have done manually is copied entire data from .xlsx file and pasted in .xlsm file (Input sheet) and once i run macro i need output like Output sheet.

Is this clear?
 
Here's something for you to work on. It is plain brute force macro. Test it on backup.

Code: 
Public Sub CleanUpSheet()
Const strDelColNo As String = "1,2,3,5,6,8,9,10,12,13,14,18,22,23,24,26,27,28,29"
Dim varDelCol As Variant
Dim i As Long

varDelCol = Split(strDelColNo, ",")

Application.ScreenUpdating = False
For i = UBound(varDelCol) To LBound(varDelCol) Step -1
    Cells(1, CLng(varDelCol(i))).EntireColumn.Delete
Next i

For i = Cells.Find("*", Cells(1, 1), , , , xlPrevious).Row To 6 Step -1
    If Len(Cells(i, 1).Value) = 0 Then
        Cells(i, 1).EntireRow.Delete xlUp
    Else
        If Cells(i, 1).Value = "CoCd" Then
            Cells(i, 1).EntireRow.Delete xlUp
        End If
    End If
Next
Cells(1, 1).Resize(4, 1).EntireRow.Delete

Application.ScreenUpdating = True

End Sub
 
You must log in or register to reply here.
Back
Top