VBA

[Betty Csehi]

I believe I need a VBA to do what I want. Since I'm not good at VBA, I'm
hoping someone out there can help me out. Here's what I have in Column A:

1)Rent Type
2)Date
3)Category
4)
5)Recourse
6)12/31/04
7)Net
8)
9)Non-Recourse
10) 6/30/05
11) Full

I imported this info and what happened is that the program stacked 3 areas
of info into one column. What I want to do is break that out into 3
separate columns. So Col A will be Rent, Col B will be Type and Col C will
be Category. The next 3 rows is the associated info.

I can breakout the column headings, but can a formula be written that will
put what is in row 5 under column A, row 6 under col B and row 7 under col
C. It would then skip Row 8, put what is in row 9 under col A, what is in
row 10 under col B and what is in row 11 under col C.

I have approximately 500 rows of data.

Thanks!

 

[Dave O]

Hi, Betty-
No VBA required: here's a formulaic workaround for you.

Step 1: Make a backup copy of your data, and keep your original data
intact! That way if something goes horribly wrong you can revert to
your original information.

Step 2: Insert a blank row at the beginning of your data. The mockup I
created shows the Rent Type column header in A2, Date in A3, and
Category in A4. The first group of sample data, Recourse, 12/31/04,
and Net appear in A6, A7, and A8, etc.

Step 3: Enter this formula in cell B2:
=IF(A1="",A2,"")

Enter this formula in C2:
=IF(A1="",A3,"")

Enter this formula in C3:
=IF(A1="",A4,"")

Copy these formulas, and paste them into all the rows of your
spreadsheet.

The formulas look for the blank row in between groups of data, and
transpose them into rows. The result will show a series of blank rows,
however; copy columns B, C, and D, then paste them back over themselves
at values to convert them formulas into static values. Then delete the
extra rows.

 

[Bernie Deitrick]

Betty,

Sub BettysMacro()
Dim Rng As Range
Dim Dest As Range
Dim myArea As Range
Dim i As Integer

Set Rng = ActiveSheet.Range("A:A"). _
SpecialCells(xlCellTypeConstants, 23)

For Each myArea In Rng.Areas
Set Dest = ActiveSheet.Range("B65536").End(xlUp)(2)
For i = 1 To myArea.Cells.Count
Dest(1, i).Value = myArea(i, 1).Value
Next i
Next myArea

ActiveSheet.Range("A:A").EntireColumn.Delete

End Sub

HTH,
Bernie
MS Excel MVP

 

[Bob Phillips]

Hi Betty,

VBA it is

Sub MoveData()
Dim iLastRow As Long
Dim i As Long
Dim iRow As Long

iRow = 1
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow Step 4
Cells(iRow, "A").Value = Cells(i, "A").Value
Cells(iRow, "B").Value = Cells(i + 1, "A").Value
Cells(iRow, "C").Value = Cells(i + 2, "A").Value
iRow = iRow + 1
Next i
Range(Cells(iRow, "A"), Cells(iLastRow, "A")).ClearContents

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Betty Csehi]

Thanks for the help and quick response - it is most appreciated!