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
        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").Sheets(wsArray(i)).Move after:=Workbooks("Book.xlsx").Sheets(1)
    Next i
    On Error GoTo 0
End Sub
Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *