I need a VBA code in excel to accomplish the following assignment:. We are given

Question

I need a VBA code in excel to accomplish the following assignment:.

We are given a spreadsheet for processing and use that has blank rows in it which we wish to eliminate.

Gather data from the worksheet into array form in the memory. You will need to use multiple arrays because the spreadsheet has two different types of data (text string, and numerical values) and you can’t mix data types within a given array. Skip over the blank rows.

• Once all your data is in the computer memory, clear the worksheet.

• Rewrite the data to the worksheet, without the empty rows.

• PRO TIP: – Sheet Clears and Rewrites done by VBA can’t be undone with the undo button in Excel. – When you are in develop/test mode, don’t clear the worksheet, and rewrite the data into columns 7-10 (for example) so you can see both old and new formatting at the same time. This way you don’t erase anything you are still working on. – Once you are sure it’s working correctly, add in the Sheet1.Cells.Clear statement, and then put your output in columns 1-4 instead.

Month January February March April May June July August September October November December Widgets Doodads Thingies 1846 2951 8356 7388 1273 4214 3360 9619 8372 2309 9996 6583 2473 9942 8259 4335 1902 7247 8591 5077 5197 5463 6556 6019 1405 5595 9135 9692 9025 2477 3465 6138 4587 5909 7744 3321

Explanation / Answer

Program Code:

/*Please note that the following code assumes that the first row in the excel is reserved for the column headings. Name of the macro is removeBlanks().*/

'Variable declarations
Dim iMaxRows As Integer
Dim iRow As Integer, iArrRow As Integer
Dim strMonth() As String
Dim intMonthData() As Integer
Sub removeBlanks()
    'Following code dyanamically finds the last used row
    iMaxRows = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
  
    iArrRow = 0
  
    'For loop for moving data from Excel to the array by eliminating blanks
    For iRow = 2 To iMaxRows
        If Trim(Sheet1.Cells(iRow, 1)) <> "" Then
            iArrRow = iArrRow + 1
            ReDim Preserve strMonth(iArrRow)
            ReDim Preserve intMonthData(3, iArrRow)
            strMonth(iArrRow - 1) = Sheet1.Cells(iRow, 1)
            intMonthData(0, iArrRow - 1) = Val(Sheet1.Cells(iRow, 2))
            intMonthData(1, iArrRow - 1) = Val(Sheet1.Cells(iRow, 3))
            intMonthData(2, iArrRow - 1) = Val(Sheet1.Cells(iRow, 4))
        End If
    Next iRow
  
    'Following lines will select the sheet as a whole and clears it
    Sheet1.Select
    Sheet1.Cells.Clear
  
    'Column Headings
    Sheet1.Cells(1, 1) = "Month"
    Sheet1.Cells(1, 2) = "Widgets"
    Sheet1.Cells(1, 3) = "Doodas"
    Sheet1.Cells(1, 4) = "Thingies"
  
    'For loop to move data from Array to the Excel
    For iRow = 0 To UBound(strMonth) - 1
        Sheet1.Cells(iRow + 2, 1) = strMonth(iRow)
        Sheet1.Cells(iRow + 2, 2) = intMonthData(0, iRow)
        Sheet1.Cells(iRow + 2, 3) = intMonthData(1, iRow)
        Sheet1.Cells(iRow + 2, 4) = intMonthData(2, iRow)
    Next iRow
  
    MsgBox "Removal of Blank Lines completed!!!"
End Sub

Output Screenshot:

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.