Solved

Row Delete Via VBA For Numbers Of .CSV

Posted on 2014-11-03
26
410 Views
Last Modified: 2014-12-16
Hi Experts,

Need a help on piece of code to delete rows from .csv files. please help me out.
I have numbers of .csv file in one location one folder, need to delete rows which is not fit in criteria.

.csv FileAbove is one .csv file screen shot need to delete rows in which column is not equal to EQ or BE i.e. other than "EQ" or "BE" delete except header. there is number of .csv file in one location. need to do it for all that files.

Thanks
0
Comment
Question by:itjockey
  • 16
  • 10
26 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40420525
Hello,

They are all in Col B ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40420527
... and could you post one of them pls.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 40420855
Yes all are in column  B.

attached is both files - raw file and after processing file which I needed.


Thank you & happy to see you again after very long time.  :)
cm24JUN2014bhav.csv
cm24JUN2014bhav-after.csv
0
 
LVL 8

Author Comment

by:itjockey
ID: 40421896
Sir. Gowflow,
do u need more clarification on this?

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40422022
ok here is it.

The below code is the main core of the routine. But in the workbook there is more than that there is the following:

1) Trace to see what files are being processed and in what directory.
2) a worksheet called Delete Row where we can put the criteria there you will see I put <>BE <>EQ you can put different but it goes 2 by 2. It will work in this case if you have more I need to test the behavior let me know.
3) You can choose a directory to process and even if it has SUBDIRECTORIES it will process them as well !!! quite powerful.
4) Also it has an Audit sheet where it will log all the delete items that it will process.
5) Select the directory you want to process from the Grey button then once done follow below before you activate the red button Delete Rows


MY SUGGESTION:

1) Either you make a copy of the current directory that you want to process and run the macro on it.

or

2) Change this line in the below code
from
'---> Save workbook
WB.Close savechanges:=True

to
'---> Save workbook
WB.Close savechanges:=False

Run the macro and look at the results in sheet audit it will put the rows that will be deleted in Column D and the total rows per file in Col C, if all is ok (after you run it it will not save the data in the original file ie the rows will not be deleted) so if all is ok then change it back to

'---> Save workbook
WB.Close savechanges:=True

and then run the macro.

here is the main code.
Function DelSpecRowCSV() As String
On Error GoTo ErrHandler

Dim WB As Workbook
Dim WS As Worksheet
Dim WSDelRows As Worksheet
Dim WSAudit As Worksheet
Dim WSMain As Worksheet
Dim MaxRow As Long, MaxCol As Long, MaxRowA As Long, MaxRowE As Long, MaxRowM As Long, I As Long
Dim lUns As Long, lRows As Long
Dim Rng As Range, cRow As Range
Dim sFile As String, sDirName As String
Dim colFiles As New Collection
Dim vFile As Variant
    
'---> Set Variables
Set WSDelRows = Sheets("Delete Rows")
MaxRowE = WSDelRows.Range("A" & WSDelRows.Rows.Count).End(xlUp).Row
Set WSAudit = Sheets("Audit")
MaxRowA = WSAudit.Range("A" & WSAudit.Rows.Count).End(xlUp).Row
If MaxRowA = 1 Then MaxRowA = MaxRowA + 1
Set WSMain = ActiveSheet

'---> Get the Recursive Files and folders
RecursiveDir colFiles, gstDestinationFolder, "*.csv", True

'---> Clean Previous Trace
WSMain.Range("B14:I" & WSMain.Rows.Count).ClearContents
MaxRowM = 14


For Each vFile In colFiles
           
    '---> Get full name
    sFile = Dir(vFile)
    sDirName = Mid(vFile, 1, InStrRev(vFile, "\"))
    
    '---> Update Trace
    WSMain.Cells(MaxRowM, "B") = sDirName
    WSMain.Cells(MaxRowM, "C") = sFile
    
    '---> Disable Events
    With Application
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    '---> Open workbook and affect variables
    Set WB = Workbooks.Open(vFile)
    Set WS = WB.ActiveSheet
    MaxRow = WS.UsedRange.Rows.Count
    MaxCol = WS.UsedRange.Columns.Count
    WSMain.Activate
    
    '---> enable Trace
    With Application
         .ScreenUpdating = True
    End With
    
    '---> Loop Thru all the Criteria
    For I = 2 To MaxRowE Step 2
        If WS.AutoFilterMode = True Then WS.ShowAllData
        WS.Range("B1").AutoFilter field:=WSDelRows.Cells(I, "B"), Criteria1:=WSDelRows.Cells(I, "A"), Operator:=xlAnd, Criteria2:=WSDelRows.Cells(I + 1, "A")
    
        '---> Set the Current Range
        Set Rng = WS.Range(WS.Range("A2"), WS.Cells(MaxRow, MaxCol)).EntireRow.SpecialCells(xlCellTypeVisible)
        
        '---> Count the number of Rows
        lRows = 0
        For Each cRow In Rng.EntireRow
            lRows = lRows + 1
        Next cRow
        
        '---> Register the record found in Audit
        WSAudit.Cells(MaxRowA, "A") = Now
        WSAudit.Cells(MaxRowA, "B") = sFile
        WSAudit.Cells(MaxRowA, "C") = lRows
        WSAudit.Cells(MaxRowA, "D") = Rng.Address
        MaxRowA = MaxRowA + 1
        lUns = lUns + 1
        
        '---> Update Trace Deleted row count
        WSMain.Cells(MaxRowM, "E") = lRows
            
        '---> Delete all Rows
        Rng.Delete
        WS.ShowAllData
        WS.AutoFilterMode = False
        WS.UsedRange.EntireColumn.AutoFit
        
        '---> Update Trace rec count
        WSMain.Cells(MaxRowM, "D") = I
        DoEvents
    Next I
    
    
    '---> Update Trace Status
    WSMain.Cells(MaxRowM, "F") = "Done"
        
    '---> Save workbook
    WB.Close savechanges:=True
    
    '---> reset Variables
    Set WS = Nothing
    Set WB = Nothing
    lUns = 0
    lRows = 0
    MaxRowM = MaxRowM + 1
    
    '---> Enable Events
    With Application
        .EnableEvents = True
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
Next vFile

'---> fix Layout
WSAudit.UsedRange.EntireColumn.AutoFit

'---> Set Flag to complete successful and exit
DelSpecRowCSV = ""
Exit Function

ErrHandler:
MsgBox (Error(Err))
DelSpecRowCSV = Error(Err)
On Error GoTo 0

End Function

Public Function RecursiveDir(colFiles As Collection, _
                              strFolder As String, _
                              strFileSpec As String, _
                              bIncludeSubfolders As Boolean)

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant

'---> Add files in strFolder matching strFileSpec to colFiles
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
    colFiles.Add strFolder & strTemp
    strTemp = Dir
Loop

If bIncludeSubfolders Then
    '---> Fill colFolders with list of subdirectories of strFolder
    strTemp = Dir(strFolder, vbDirectory)
    Do While strTemp <> vbNullString
        If (strTemp <> ".") And (strTemp <> "..") Then
            If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
                colFolders.Add strTemp
            End If
        End If
        strTemp = Dir
    Loop

    '---> Call RecursiveDir for each subfolder in colFolders
    For Each vFolderName In colFolders
        Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
    Next vFolderName
End If

End Function

Open in new window



gowflow
DelSpecificRowCSV-V01.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 40423320
Sir.Gowflow,

Working Perfect & good interface .....really loving it...only Two thing need to add.
A) if there is not found any instance other than "EQ" or "BE" it come up with error "No Cells Found" & process stops. There is some files which have only "EQ & BE" only, need to by pass that file and go on for next.

B) Delete or say clear button which clear all data on main sheet as well as in Audit sheet.

Do I add more criteria on sheet "Delete Row"? if possible add 2 or 3 more .....not required right now but may be in future .....chances is so less ....so if it will take to much time to code this ...then ignore this.

Thank You Very Much
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40423416
Here it is
I added 2 buttons Clean Trace and Reset Audit and now it takes into consideration the items not found.
Pls chk and let me know.

For more than 2 criterias we will need a new question as need to elaborate and extend routine further and more tests.

Let me know
gowflow
DelSpecificRowCSV-V02.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 40423459
Sure for more condition new question.

getting error while running this code .....Error & this is the file which Is opened at that point of timefile
I had clicked new two buttons twice (working fine) and re run the code ....each time file is different which generate error.


Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40423493
can you post the file that generated the error please.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 40424517
Extremely Sorry For Delay, here is the file which generate error. This is untouched file.


Thanks
cm11NOV2012bhav.csv
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40424537
I am not getting an error. What Excel version are you running ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 40424721
Excel 2010
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40424879
I also have 2010. Don't understand what you are doing to get the error.

1) Close all excel
2) Run the workbook
3) Enable macros
4) Select the folder
5) Press on Delete Rows.

Did the routine complete successfully ? any error when doing it in this order ?
BTW did you change anything to the criteria ? it should be maximum 2 criterias !!!! if more maybe it will not work (not tested)

gowflow
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Author Comment

by:itjockey
ID: 40424960
Closed all WB same error Code line errorFile which is processing at that point of timeFile screen shot
Do you want me to attached all file?


Thank you
cm16MAY2010bhav.csv
0
 
LVL 8

Author Comment

by:itjockey
ID: 40424971
BTW did you change anything to the criteria ? it should be maximum 2 criterias !!!! if more maybe it will not work (not tested)

Nope - as it is

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 40424982
Rerun the code for same directory but now no error...I don't know why?
0
 
LVL 8

Author Comment

by:itjockey
ID: 40425049
Mean while may I ask new question? if it relates to yours expertise ....then pls help me out.


Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 40425064
Here is the Link.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40425670
You highlighted the error but never told me what is the error !!! what does it say ? pls tell me the error that comes up on the msgbox says what ?
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40425674
Try this version not sure if it will fix it.
gowflow
DelSpecificRowCSV-V03.xlsm
0
 
LVL 8

Author Comment

by:itjockey
ID: 40425678
I had investigated problem arise with those files which have only "EQ" and/or "BE" in column B. I had sorted all this files and put in different folder and run this macro on that folder ......surprisingly no error....I don't know why ? If run macro with all files then generat error for this kind of file but if we sort this kind of file in one folder and run macro for this kind of files ....no error.
0
 
LVL 8

Author Comment

by:itjockey
ID: 40425680
Let me check .....I had posted my comment and seen your post.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40425712
Then I think it is solved in version V03 as I was not resetting the variable Rng that hold the Rang filtered which presume was carrying over from last file that had other occurrences other than EQ or BE thus when it hit a file that does not have but these 2 occurrences instead of showing Nothing it is showing a value hence going into error !

Sorry for that.
gowflow
0
 
LVL 8

Author Closing Comment

by:itjockey
ID: 40427618
Perfect
0
 
LVL 8

Author Comment

by:itjockey
ID: 40500871
Sir.Gowflow,

I don't know why after 30 days of break ....all buttons are disabled i.e. doesn't work. regarding this I had raised new question please look in to this.Code Help

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 40504075
really need your assistance ...please
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Outlook Free & Paid Tools
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now