VBA example - Excel 2007: loop to rearrange data in cells

VBA code to rearrange data in cells

In this example, We will use a VBA loop (for loop) to rearrange information in the range of cells in Excel sheet. Before rearranging, the information display in the cells in a messy matter in which some cells are unnecessary blank. See the example text below:

VBA for Excel
Excel 2007
VBA array
VBA loop
Microsoft Excel 2007
VBA loop
VBA for Access
VBA programming for Excel
VBA example code
VBA example code
Rearrange information with VBA code

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

VBA for Excel: VBA loop to locate and fill color the last row that contains data

VBA code for this example:

Option Explicit Private Sub CommandButton1_Click()
    Dim i, j As Integer
    j = 1
    For i = 1 To 100 'Test 100 rows of data. You can make change.
        If Trim(Cells(i, 1)) <> "" Then
          Cells(j, 2) = Cells(i, 1) 'fetch data from the non-blank cells
          j = j + 1
        End If
    End Sub


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.