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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
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:

excel33452

New Member
Joined
Apr 29, 2014
Messages
2
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)?
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,362
Members
414,306
Latest member
Dennis_vdw

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
Top