Row Delete Via VBA For Numbers Of .CSV

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
LVL 8
Naresh PatelTraderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
Hello,

They are all in Col B ?
gowflow
0
gowflowCommented:
... and could you post one of them pls.
gowflow
0
Naresh PatelTraderAuthor Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Naresh PatelTraderAuthor Commented:
Sir. Gowflow,
do u need more clarification on this?

Thanks
0
gowflowCommented:
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
Naresh PatelTraderAuthor Commented:
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
gowflowCommented:
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
Naresh PatelTraderAuthor Commented:
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
gowflowCommented:
can you post the file that generated the error please.
gowflow
0
Naresh PatelTraderAuthor Commented:
Extremely Sorry For Delay, here is the file which generate error. This is untouched file.


Thanks
cm11NOV2012bhav.csv
0
gowflowCommented:
I am not getting an error. What Excel version are you running ?
gowflow
0
Naresh PatelTraderAuthor Commented:
Excel 2010
0
gowflowCommented:
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
Naresh PatelTraderAuthor Commented:
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
Naresh PatelTraderAuthor Commented:
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
Naresh PatelTraderAuthor Commented:
Rerun the code for same directory but now no error...I don't know why?
0
Naresh PatelTraderAuthor Commented:
Mean while may I ask new question? if it relates to yours expertise ....then pls help me out.


Thanks
0
Naresh PatelTraderAuthor Commented:
Here is the Link.

Thanks
0
gowflowCommented:
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
gowflowCommented:
Try this version not sure if it will fix it.
gowflow
DelSpecificRowCSV-V03.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naresh PatelTraderAuthor Commented:
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
Naresh PatelTraderAuthor Commented:
Let me check .....I had posted my comment and seen your post.

Thanks
0
gowflowCommented:
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
Naresh PatelTraderAuthor Commented:
Perfect
0
Naresh PatelTraderAuthor Commented:
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
Naresh PatelTraderAuthor Commented:
really need your assistance ...please
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.