Hi,

Try the code fixed, let me know.

The Runtime Error 6 (Overflow) occurs because you're using Integer data type for RowNum which has a maximum value of 32,767. When the calculation involves large numbers, it can overflow.

 fix; rowNum As Long > Changed from Integer to Long

topN As Long > Changed from Integer to Long

sizeInBytes As Double > Added explicit variable for clarity

Used 1024# > The # ensures Double type in division to prevent overflow.

Option Explicit

' Function to recursively get the size of a folder and all its subfolders
Public Function GetFolderSize(ByVal folderPath As String) As Double
    Dim fso As Object
    Dim folder As Object
    Dim subFolder As Object
    Dim file As Object
    Dim totalSize As Double

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder(folderPath)

    ' Add the size of files in the current folder
    For Each file In folder.Files
        totalSize = totalSize + file.size
    Next file

    ' Recursively call for subfolders
    For Each subFolder In folder.SubFolders
        totalSize = totalSize + GetFolderSize(subFolder.Path)
    Next subFolder

    GetFolderSize = totalSize

    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing
End Function

' Subroutine to find the largest folders and display results in an Excel sheet
Sub FindLargestFolders()
    Dim fso As Object
    Dim parentFolder As Object
    Dim subFolder As Object
    Dim folderPath As String
    Dim rowNum As Long '  CHANGED FROM INTEGER TO LONG 
    Dim ws As Worksheet
    Dim folderSizes() As Variant
    Dim i As Long
    Dim j As Long
    Dim temp As Variant
    Dim topN As Long '  CHANGED FROM INTEGER TO LONG 
    Dim sizeInBytes As Double '  ADDED FOR CLARITY 

    ' Define the parent directory to scan
    folderPath = "C:\Users\jose.rossi\Desktop\Excel Files" ' << CHANGE THIS PATH

    ' Define how many top folders to list
    topN = 10

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Clear previous results
    ws.Cells.Clear

    ' Set headers
    ws.Cells(1, 1).Value = "Folder Path"
    ws.Cells(1, 2).Value = "Size (Bytes)"
    ws.Cells(1, 3).Value = "Size (MB)"
    ws.Cells(1, 4).Value = "Size (GB)"

    rowNum = 2

    ' Get the parent folder object
    If Not fso.FolderExists(folderPath) Then
        MsgBox "Folder not found: " & folderPath
        Exit Sub
    End If
    Set parentFolder = fso.GetFolder(folderPath)

    ' Store folder paths and sizes in an array/collection for sorting
    Dim colFolders As New Collection

    For Each subFolder In parentFolder.SubFolders
        Dim size As Double
        size = GetFolderSize(subFolder.Path) ' Call the recursive function
        colFolders.Add Array(subFolder.Path, size)
    Next subFolder

    ' Convert collection to array for easier sorting
    ReDim folderSizes(1 To colFolders.Count, 1 To 2)
    For i = 1 To colFolders.Count
        folderSizes(i, 1) = colFolders(i)(0) ' Path
        folderSizes(i, 2) = colFolders(i)(1) ' Size
    Next i

    ' Simple Bubble Sort for descending order
    For i = 1 To UBound(folderSizes, 1) - 1
        For j = i + 1 To UBound(folderSizes, 1)
            If folderSizes(i, 2) < folderSizes(j, 2) Then
                ' Swap elements
                temp = folderSizes(i, 1)
                folderSizes(i, 1) = folderSizes(j, 1)
                folderSizes(j, 1) = temp
                temp = folderSizes(i, 2)
                folderSizes(i, 2) = folderSizes(j, 2)
                folderSizes(j, 2) = temp
            End If
        Next j
    Next i

    ' Display the top N results in the worksheet
    For i = 1 To Application.WorksheetFunction.Min(topN, UBound(folderSizes, 1))
        sizeInBytes = CDbl(folderSizes(i, 2)) '  ENSURE IT'S DOUBLE 
        
        ws.Cells(rowNum, 1).Value = folderSizes(i, 1) ' Path
        ws.Cells(rowNum, 2).Value = sizeInBytes ' Size in Bytes
        ws.Cells(rowNum, 3).Value = Round(sizeInBytes / 1024# / 1024#, 2) ' Size in MB
        ws.Cells(rowNum, 4).Value = Round(sizeInBytes / 1024# / 1024# / 1024#, 2) ' Size in GB
        rowNum = rowNum + 1
    Next i

    ' Format columns
    ws.Columns("A:D").AutoFit

    MsgBox "Top " & topN & " largest folders listed successfully!"

    Set parentFolder = Nothing
    Set fso = Nothing
End Sub