Add a line below if a cell is not blank

excel33452

New Member
Joined
Apr 29, 2014
Messages
2
I am trying to stack the values of a chart into a single column.
So my data looks like this:
-------A--------B
1 Value1 Value2
2 Value3
3 Value4
4 Value5 Value6
5 Value7

If cell 1B does not = 0 I want to add a line between line 1 and 2 (I'll call that line "11") and take Value2 and paste it into 11A If cell 1B is blank I want it to do nothing
So I want my data to look like this:

---------A
1....Value1
11..Value2
2....Value3
3....Value4
4....Value5
41..Value6
5....Value7

I want to automate this as much as possible but if it's not easy to do I at least want to at least automatically add a blank row below if value does not = 0 and then I can manually move the values as needed.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Welcome to the board. Try:
Code:
Sub InsertBlankLine()

Dim rng As Range

Application.screenUpdating = False

With ActiveSheet
    Set rng = .Range("B" & .Rows.Count).End(xlUp)
    Do Until rng.Row = 1 And rng.Value <> 0
        rng.Offset(1).EntireRow.Insert
        .Range("A" & rng.Row + 1).Value = rng.Value
        rng.clearcontents
        Set rng = rng.End(xlUp)
    Loop
End With

Application.screenUpdating = True

Set rng = Nothing

End Sub
 
Last edited:
Upvote 0
Thank you so much for your help.
I used this code and it created blank line every other row - is it possible to automatically copy-paste into this line the values of the row above if a certain condition is met? (in my case if a cell is not blank)?
 
Upvote 0
The code works fine for me, I used a dummy test sheet and seemed fine, check your values in column B e.g. are the cells completely empty or contain hidden characters.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top