Excel VBA change cell range to array

Q&A_ExcelVBA_Q002

Q&A_ExcelVBA_Q003

 

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
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 *