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

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

HTML Code:

---

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

---

Try this

Code:

---

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

---

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

Add this line after the sorting

Code:

---

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

---

Or

Code:

---

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

---

Hi Excelfox

Thanks for all the assistance, which is highly appreciated