VBA for Excel 2007 tutorial-VBA arrays to reverse data in rows


VBA arrays to reverse data in rows

In this example, We use VBA array to display the text in 5 rows of Excel worksheet in reverse order.

For example, the original text in the rows are:
Excel 2007 tutorial
VBA array
VBA loop
VBA for Excel 2007
Learn VBA code

After running the VBA example code, the text will look like this:

Learn VBA code
VBA for Excel 2007
VBA loop
VBA array
Excel 2007 tutorial


To run this example VBA code, you will need a Form, and a CommandButton. You may type the sample text in to your current worksheet.


VBA for Excel: using VBA array to display text in rows reversely

VBA code for this example:


Private Sub CommandButton1_Click()
    Dim myarr(5) As String
    Dim i
    i = 1
    'read names from Excel sheet and store them in the array
    While Cells(i, 1) <> ""
        myarr(i - 1) = Cells(i, 1)
        i = i + 1
    Wend
   
    'Reversing the order
    i = 1 'reset i
    Do Until i = 3
      
           Dim temp As String
            temp = myarr(i - 1) 'switching the names
            myarr(i - 1) = myarr(5 - i)
            myarr(5 - i) = temp
           i = i + 1
    Loop
   
    'Show the reversed array
    i = 1 'reset i
   
    For Each x In myarr
        Cells(i, 1) = x
        i = i + 1
    Next
   
End Sub


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.