VBA code amend help!`

[Fuisdale2]

Hi my peers,

I found the below code from another forum and I have tried to amend to suit my needs to no avail.

The code will loop through a folder and list the file name and is supposed to count the number of rows. However the code isn't correctly counting the number of rows with data.

Can I please get your advice and guidance to resolve my issue.

Sub SO()
     
    Dim MyFolder As String, matchFileSpec As String
    Dim checkSubFolders As Boolean
    Dim x() As String
    Dim returnVal As Variant
    Dim WSS As Object
     
    Worksheets("Sheet2").Activate
    Range("A2").Value = Time
     
    Set WSS = CreateObject("WScript.Shell")
     
    MyFolder = "C:\Users\PAH\Desktop\New folder" '// Change as required.
    checkSubFolders = False '// Change as required
     'Worksheets("Sheet1").Activate
     
    MyFolder = MyFolder & IIf(Right(MyFolder, 1) = "\", "", "\")
    matchFileSpec = MyFolder & "*.xlsx"
     
    x = Filter(Split(WSS.Exec("CMD /C DIR """ & matchFileSpec & """ /B" & IIf(checkSubFolders, " /S", "") & " /A:-D").StdOut.ReadAll, vbCrLf), ".xlsx", True, vbTextCompare)
     
    For Each returnVal In x
        With Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            .Value = returnVal
            .Offset(0, 1).Value = Split(WSS.Exec("CMD /C FindStr /R /N ""^"" """ & MyFolder & returnVal & """ | %WINDIR%\System32\find /C "":""").StdOut.ReadAll, vbCrLf)(0)
        End With
    Next returnVal
     
    Set WSS = Nothing
     
    Worksheets("Sheet2").Activate
    Range("C2").Value = Time
     
End Sub
Sub snb()
    c01 = "C:\Users\PAH\Desktop\New folder"
    c02 = Dir("C:\Users\PAH\Desktop\New folder*.xlsx")
     
    Do Until c02 = ""
        With Workbooks.Open(c01 & c02, , , 1).Sheets(1).UsedRange
            ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(.Rows.Count, .Columns.Count) = .Value
            .Parent.Parent.Close
        End With
        c02 = Dir
    Loop
End Sub
Sub OpenFiles()
    Dim MyFolder As String
    Dim myFile As String
    Dim TargetWB As Workbook
    MyFolder = GetFolder("C:\Users\PAH\Desktop\New folder") 'Modify as needed.
    myFile = Dir(MyFolder & "\*.xlsx") 'Modify as needed.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Do While myFile <> ""
        Set TargetWB = Workbooks.Open(Filename:=MyFolder & "\" & myFile)
        With TargetWB
            If CountUsedRows(TargetWB) > 1 Then
                .SaveAs "C:\Users\PAH\Desktop\New folder" & myFile 'Modify as needed.
            End If
            .Close
        End With
    myFile = Dir
    Loop
    Shell "explorer.exe C:\Users\PAH\Desktop\New folder", vbMaximizedFocus 'Open the folder.
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
Function GetFolder(strPath As String) As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function
Function CountUsedRows(Wbk As Workbook) As Long
    Dim WS As Worksheet
    Set WS = Wbk.Sheets(1)
    CountUsedRows = WS.Range("A" & Rows.Count).End(xlUp).Row 'Modify as necessary.
End Function