I have created a macro with some help with the wonderful people of this website that figures out if columns in this excel are filled and then separates them for a bigger project. my problem is that the code just crashes (loads until I click in which I'm told excel is unresponsive) excel. could I get some help

Option Explicit

Public Sub emptysinder()

    Dim i As Long
    Dim r As Range
    Dim num As Integer
    
    For i = 1 To 9
        Set r = Range("F1").Offset(0, i - 1).Resize(200, 1)
        If IsAllEmpty(r) Then
            num = num   1
            Debug.Print "Range " & r.Address & " is all empty." & num
        ElseIf IsAnyEmpty(r) Then
            Debug.Print "Range " & r.Address & " is partially empty."
        Else
            Debug.Print "Range " & r.Address & " filled."
        End If
    Next i
    split (num)

End Sub

Public Function IsAllEmpty(ByVal r_range As Range) As Boolean
    Dim Item As Range
    For Each Item In r_range
        If Not IsEmpty(Item) Then
            IsAllEmpty = False
            Exit Function
        End If
    Next
    IsAllEmpty = True
End Function

Public Function IsAnyEmpty(ByVal r_range As Range) As Boolean
    Dim Item As Range
    For Each Item In r_range
        If IsEmpty(Item) Then
            IsAnyEmpty = True
            Exit Function
        End If
    Next
    IsAnyEmpty = False
End Function
Public Function split(i As Integer)
    Dim sheet As Integer
    Dim colOpt As Integer
    sheet = 14
    colOpt = sheet - i
    Dim s As Integer
    Do While i > 0
        For s = 4 To 0
        Columns(colOpt).Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        i = i - 1
        Next s
    Loop
End Function

CodePudding user response：

As Shrotter said above, your for loop in the split function isn't executing because the s value is incrementing, not decrementing. Use For s = 4 To 0 Step -1 to decrease s by 1 every iteration.