VBA Macro To Open And Modify Files By Looping Through Files In Folder

[Howardc]

Hi Excel Fox

I had a rethink. Where there are Col A is blank, then the entire row must be deleted for Eg if A7 , A11 is blank then these rows must be deleted, not hidden

I have tried to write the code, but cannot get it to work.

I have attached 3 CSV files-one containing raw data, one after running the macro and one showing what the data should look like after running the macro



Your assistance in this regard is most appreciated

Sub OpenAndModifySameFileTypes()
     
    Application.ScreenUpdating = False
    Dim strFile As String
    Dim strFileType As String
    Dim strPath As String
    Dim lngLoop As Long
    

    strPath = "C:\PINNACLE Journal TEMPLATES"
    strFileType = "*.csv" 'Split with semi-colon if you want to specify the file types. Example ->> "*.xls;*.jpeg;*.doc;*.gif"
     
     For lngLoop = LBound(Split(strFileType, ";")) To UBound(Split(strFileType, ";"))
        strFile = Dir(strPath & "\" & Split(strFileType, ";")(lngLoop))
        Do While strFile <> ""
            With Workbooks.Open(strPath & "\" & strFile)
                With .Sheets(1)
                    .Range("B1:D" & .UsedRange.Rows.Count).NumberFormat = "0.00"
                 Sheets(1).Select
               Dim j As Integer, k As Integer
j = Range("a1").End(xlDown).Row
For k = j To 1 Step -1
If Cells(k, "B") = "" And Cells(k, "c") = "" Then
Cells(k, "A").EntireRow.Delete
End If
Range(Cells(j + 1, "A"), Cells(j + 50, "A")).EntireRow.Delete
Next k
                
                End With
                .Close 1
            End With
            strFile = Dir
        Loop
    Next lngLoop
    strFile = vbNullString
    strFileType = vbNullString
    strPath = vbNullString
    lngLoop = Empty
     
End Sub

[Excel Fox]

Try this

Sub OpenAndModifySameFileTypes()     
    Application.ScreenUpdating = False
    Dim strFile As String
    Dim strFileType As String
    Dim strPath As String
    Dim lngLoop As Long
    


    strPath = "C:\PINNACLE Journal TEMPLATES"
    strFileType = "*.csv" 'Split with semi-colon if you want to specify the file types. Example ->> "*.xls;*.jpeg;*.doc;*.gif"
     
     For lngLoop = LBound(Split(strFileType, ";")) To UBound(Split(strFileType, ";"))
        strFile = Dir(strPath & "\" & Split(strFileType, ";")(lngLoop))
        Do While strFile <> ""
            With Workbooks.Open(strPath & "\" & strFile)
                With .Sheets(1)
                    .Range("B2:D" & .UsedRange.Rows.Count).NumberFormat = "0.00"
                    .UsedRange.Sort Key1:=.Columns("A"), Order1:=1, Header:=1   
                End With
                .Close 1
            End With
            strFile = Dir
        Loop
    Next lngLoop
    strFile = vbNullString
    strFileType = vbNullString
    strPath = vbNullString
    lngLoop = Empty
     
End Sub

[Howardc]

Hi Excelfox

Thanks for the help, much appreciated. I need one more favour. I would like to delete 50 rows after the last value in Col A

[Excel Fox]

Add this line after the sorting

.Cells(1).Offset(.UsedRange.Rows.Count).Resize(50).EntireRow.Delete

[Excel Fox]

Or

.Cells(.Rows.Count).End(xlup)(2).Resize(50).EntireRow.Delete

[Howardc]

Hi Excelfox

Thanks for all the assistance, which is highly appreciated