Delete rows in multiple CSV files

[davidp13]

Good day,

I'm looking for some guidance as to why a piece of code I have does not function as expected. The code ultimately opens multiple .csv files, then looks for certain date values in Column E and decides whether to delete the row or not. The code to delete the row works fine if I run it as stand alone like below:

Sub test()
Dim LR As Long
Application.ScreenUpdating = False
LR = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
For r = LR To 2 Step -1
If ((Weekday(Date) >= 3 And Weekday(Date) <= 6 And Range("E" & r).Value <> Date - 1) Or (Weekday(Date) = 2 And _
Range("E" & r).Value <> Date - 3)) Then
Rows(r).Delete
Application.ScreenUpdating = True
End If
Next r
End Sub

However, when I add it to the code that looks through a certain folder for multiple .csv it does not complete the operation. The code is below:

Public Sub Setup1()

Dim StrFile As String
Dim LR As Long

StrFile = Dir("C:\Users\admi n\Downloads\*.csv")
While StrFile <> ""

Workbooks.Open fileName:=StrFile
Application.ScreenUpdating = False
LR = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
For r = LR To 2 Step -1
If (Range("E" & r).Value <> Date - 1) Then
Rows(r).Delete
Application.ScreenUpdating = True
End If
Next r
StrFile = Dir
Wend
End Sub

Can anyone perhaps explain to me why this does not work?

Thanks

 

[alansidman]

crosspost: https://www.excelforum.com/excel-pr...-delete-specific-rows-100s-files-for-csv.html

 

[tonyyy]

davidp13,

Maybe it is working and you're just not seeing it. Try moving the location of the "Application.ScreenUpdating = True" line...

Public Sub Setup1()
Dim StrFile As String
Dim LR As Long

StrFile = Dir("C:\Users\admi n\Downloads\*.csv")
While StrFile <> ""
    Workbooks.Open Filename:=StrFile
    Application.ScreenUpdating = False
    LR = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
    For r = LR To 2 Step -1
        If (Range("E" & r).Value <> Date - 1) Then
            Rows(r).Delete
        End If
    Next r
    [COLOR=#ff0000]Application.ScreenUpdating = True[/COLOR]
    StrFile = Dir
Wend
End Sub

Cheers,

tonyyy

 

[Macropod]

davidp13: Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[davidp13]

Thanks for the reply. I got it working by adding ActiveSheet. infront of the delete line. Works now!

Cheers

Public Sub Setup1()
Dim StrFile As String
Dim LR As Long


StrFile = Dir("C:\Users\admi n\Downloads\*.csv")
While StrFile <> ""
    Workbooks.Open Filename:=StrFile
    Application.ScreenUpdating = False
    LR = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
    For r = LR To 2 Step -1
        If (Range("E" & r).Value <> Date - 1) Then
[COLOR=#ff0000]            ActiveSheet.Rows(r).Delete[/COLOR]
        End If
    Next r
    Application.ScreenUpdating = True
    StrFile = Dir
Wend
End Sub

 

[tonyyy]

You're welcome. Glad you got it working...

 

[davidp13]

Just as a side note to whomever might find this useful. I struggled with the code to open the csv files. Got Run-time error 1004 until i update the code to open the csv files. so use the below instead.

Dim wbCSV   As Workbook
Dim fPath   As String:      fPath = "C:\Users\[add your path here]\"
Dim strFile As String


Application.ScreenUpdating = False  'speed up macro


strFile = Dir(fPath & "*.csv")


While strFile <> ""
        
        Set wbCSV = Workbooks.Open(fPath & strFile)

 

[nasib]

Good day,

I'm looking for some guidance as to why a piece of code I have does not function as expected. The code ultimately opens multiple .csv files, then looks for certain date values in Column E and decides whether to delete the row or not. The code to delete the row works fine if I run it as stand alone like below:

Sub test()
Dim LR As Long
Application.ScreenUpdating = False
LR = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
For r = LR To 2 Step -1
If ((Weekday(Date) >= 3 And Weekday(Date) <= 6 And Range("E" & r).Value <> Date - 1) Or (Weekday(Date) = 2 And _
Range("E" & r).Value <> Date - 3)) Then
Rows(r).Delete
Application.ScreenUpdating = True
End If
Next r
End Sub

However, when I add it to the code that looks through a certain folder for multiple .csv it does not complete the operation. The code is below:

Public Sub Setup1()

Dim StrFile As String
Dim LR As Long

StrFile = Dir("C:\Users\admi n\Downloads\*.csv")
While StrFile <> ""

Workbooks.Open fileName:=StrFile
Application.ScreenUpdating = False
LR = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
For r = LR To 2 Step -1
If (Range("E" & r).Value <> Date - 1) Then
Rows(r).Delete
Application.ScreenUpdating = True
End If
Next r
StrFile = Dir
Wend
End Sub

Can anyone perhaps explain to me why this does not work?

Thanks

here is video how to delete multiple rows from multiple csv or excel files
https://www.youtube.com/watch?v=2mx9bZ7xz5o