Excel VBA change cell range to array

 

Solution to change cell range to array

In the below solution, I have made a Sub to define array (defineArray), a Sub to delete worksheet(delWS), a Sub to move worksheet(moveWS)

First, I define an array at Module level, so that all Sub within the Module can access the array.

In Sub defineArray(), I put all the items from Range A4 to the last row of column A into wsArray()

In Sub delWS(), I put “On Error Resume Next” to prevent error message when Macro tries to delete a worksheet that does not exist

“Application.DisplayAlerts = False” prevents the confirmation message box to delete a worksheet

 

Dim wsArray() As Variant
Public Sub defineArray()
    For i = 4 To Range("A" & Rows.Count).End(xlUp).Row
        ReDim Preserve wsArray(i)
        wsArray(i) = Range("A" & i).Value
    Next i
End Sub

Public Sub delWS()
    On Error Resume Next
    For i = 4 To UBound(wsArray())
        Application.DisplayAlerts = False
        Sheets(wsArray(i)).Delete
        Application.DisplayAlerts = True
    Next i
    On Error GoTo 0
End Sub

Public Sub moveWS()
    On Error Resume Next
    For i = 4 To UBound(wsArray())
        Workbooks("Book1").Activate
        Workbooks("Book1").Sheets(wsArray(i)).Move after:=Workbooks("Book.xlsx").Sheets(1)
    Next i
    On Error GoTo 0
End Sub