I create reports in Cognos Impromtu that I save in Excel. I must group
certain columns in order to get information displayed properly. How do I
ungroup columns in excel. In other words, I have a number then a quantity of
blank spaces below then another number and a different quantity of blank
spaces below. I would like the excel program to repeat the above value until
it hits a value below it and then repeat that value until it hits another
value below it.

Is this possible?? Copy Paste is driving me insane. My reports can be 10-20
thousand lines long.

try this:

Private Sub CommandButton1_Click()
Dim myRow As Integer
Dim myValue As String
myRow = 1
Do Until myRow = Cells(Rows.Count, "A").End(xlUp).Row
If Cells(myRow, 1) < "" Then myValue = Cells(myRow, 1)
If Cells(myRow, 1) = "" Then Cells(myRow, 1) = myValue
myRow = myRow + 1

Loop
End Sub

change the rows.count,"A" part to match the column that contains the last
row of data and set myrow= to what row to start on.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Roger J Michaud" wrote:

I create reports in Cognos Impromtu that I save in Excel. I must group
certain columns in order to get information displayed properly. How do I
ungroup columns in excel. In other words, I have a number then a quantity of
blank spaces below then another number and a different quantity of blank
spaces below. I would like the excel program to repeat the above value until
it hits a value below it and then repeat that value until it hits another
value below it.

Is this possible?? Copy Paste is driving me insane. My reports can be 10-20
thousand lines long.

Roger,

Select the column, use Edit / Go To... Special Blanks OK. This selects the blank cells. Then
press =, the up arrow key once, and press Ctrl-Enter. Then re-select the column, copy, and
pastespecial values and you're done.

HTH,
Bernie
MS Excel MVP


"Roger J Michaud" wrote in message
...
I create reports in Cognos Impromtu that I save in Excel. I must group
certain columns in order to get information displayed properly. How do I
ungroup columns in excel. In other words, I have a number then a quantity of
blank spaces below then another number and a different quantity of blank
spaces below. I would like the excel program to repeat the above value until
it hits a value below it and then repeat that value until it hits another
value below it.

Is this possible?? Copy Paste is driving me insane. My reports can be 10-20
thousand lines long.

One way to do this manually is to press F5 (or Edit | GoTo) then click
Special then Current Region and OK. Press F5 again, followed by Special
and Blanks then OK. Then you can begin to enter a formula by pressing =
and then click on the cell above (i.e. the first cell with data in it)
then press CTRL-Enter. This formula is then entered into all the blank
cells that were highlighted, and will basically copy the value above
the blank cells. You can then fix the values by copy and paste special.

Hope this helps.

Pete

Roger J Michaud wrote:

I create reports in Cognos Impromtu that I save in Excel. I must group
certain columns in order to get information displayed properly. How do I
ungroup columns in excel. In other words, I have a number then a quantity of
blank spaces below then another number and a different quantity of blank
spaces below. I would like the excel program to repeat the above value until
it hits a value below it and then repeat that value until it hits another
value below it.

Is this possible?? Copy Paste is driving me insane. My reports can be 10-20
thousand lines long.