VBA Express : Excel - Clean Up Odd Row Data

Excel

Clean Up Odd Row Data

Ease of Use

Easy

Version tested with

2000 / 2002

Submitted by:

Jacob Hilderbrand

Description:

Formats records that contain multiple rows of a variable number.

Discussion:

You get data from a database and it comes in 3 or 4 or even 5 rows per record. You need to format this data so it's only one row per record. While it's unlikely that you'll be able to use this macro exactly as it is, this entry demonstrates how such a task can be accomplished. You could follow up this task with a Data-Text to Columns on column D to further parse the data. Always back up your data before attempting to use macros on it.

Code:

instructions for use

			Option Explicit 
 
Sub FixFormat() 
     
    Dim LastRow         As Long 
    Dim x               As Long 
     
    LastRow = Range("D65536").End(xlUp).Row 
    For x = LastRow To 2 Step -1 
        If Range("B" & x).Value = "" Then 
            Range("D" & x - 1).Value = Range("D" & x - 1).Value & " " & Range("D" & x).Value 
            Range("D" & x).EntireRow.Delete 
        End If 
    Next x 
     
End Sub

How to use:

Copy the code above.
Open the file that contains data records that take multiple rows, similar to the sample file.
Hit Alt+F11 to open the Visual Basic Editor (VBE).
From the menu, choose Insert-Module.
Paste the code into the code window that appears at right.
Hit the Save diskette and close the VBE.

Test the code:

Hit Tools-Macro-Macros and double-click FixFormat.

Sample File:

weirdata.zip 6.5KB

Approved by mdmackillop

This entry has been viewed 297 times.