Row Delete Via VBA For Numbers Of .CSV

Hello,

They are all in Col B ?
gowflow

... and could you post one of them pls.
gowflow

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

Sir. Gowflow,
do u need more clarification on this?

Thanks

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

Select allOpen in new window

gowflow
DelSpecificRowCSV-V01.xlsm

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

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

Sure for more condition new question.

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


Thanks

can you post the file that generated the error please.
gowflow

Extremely Sorry For Delay, here is the file which generate error. This is untouched file.


Thanks
cm11NOV2012bhav.csv

I am not getting an error. What Excel version are you running ?
gowflow

Excel 2010

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

Closed all WB same error
Do you want me to attached all file?


Thank you
cm16MAY2010bhav.csv

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

Rerun the code for same directory but now no error...I don't know why?

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


Thanks

Here is the Link.

Thanks

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

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.

Let me check .....I had posted my comment and seen your post.

Thanks

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

Perfect

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

really need your assistance ...please