I have a report that is exported to Excel using Crystal Reports. There are
about 18,000 entries. The report puts the Item Category in the row above each
section of items. I need it to go in the same row, only in the column in
front of it. In other words, there may be 30 different types of screws, but
they are all under the SCREWS category. The report puts SCREWS the section as
a title. I need it to go in the column beside the description of the screw.
There are over 500 different categories. Any help is appreciated.

Hi Wayne,
I had to make a few assumptions:
1. The "Item Category" is in the first column, i.e. A1, A50, etc.
2. The cell to the immediate right of the "Item Category" (B1, B50, etc.) is
empty.
3. Except for the "Item Category" rows, all the other rows have data in
column B.

Given those assumptions, this macro may do what you want. It will put the
"Item Category" in front of each row and will delete the rows that have the
"Item Category" in the header.

Option Explicit
Dim ILoop As Integer
Dim NumRows As Integer
Dim Title As String
Sub AddTitles()

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
NumRows = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
Columns("A").Insert

For ILoop = 1 To NumRows
If IsEmpty(Cells(ILoop, "C")) Then
Title = Cells(ILoop, "B")
End If
Cells(ILoop, "A") = Title
Next ILoop

For ILoop = NumRows To 1 Step -1
If IsEmpty(Cells(ILoop, "C")) Then
Rows(ILoop).Delete
End If
Next ILoop

'Turn on warnings, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Use Alt+F11 to open the Visual Basic Editor.
Go to Insert Module and paste this code into it.
Be sure to back up your data before trying the macro.

--
Ken Hudson


"Wayne via OfficeKB.com" wrote:

I have a report that is exported to Excel using Crystal Reports. There are
about 18,000 entries. The report puts the Item Category in the row above each
section of items. I need it to go in the same row, only in the column in
front of it. In other words, there may be 30 different types of screws, but
they are all under the SCREWS category. The report puts SCREWS the section as
a title. I need it to go in the column beside the description of the screw.
There are over 500 different categories. Any help is appreciated.