I am trying to insert columns between positive and negative

DevintheDude

Board Regular
Joined
Jun 8, 2007
Messages
153
I want to search a column from - to 0 to + and I want to insert columns between the - and + or if their is 0's insert columns between 0 and +

What kind of method formulas can do this
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hello DevintheDude,
I'm assuming you're looking for a vba solution as there is no way to achieve this with
any kind of formula(s)

I'm going to need a bit of clarification before being able to provide a meaningful
example.


I want to search a column from - to 0 to + and I want to insert columns between the - and + or if their is 0's insert columns between 0 and +
You want to:
Search a column & insert one or more row(s)? or
Search a row & insert one or more column(s)?

While searching, say it finds a negative number and then a positive number:
(Do the insert?)
Or, say it finds a zero and then a negative number. . . (?)
Or, . . . a negative, a zero, then a negative. . . ?

Can you narrow this down a bit?
 
Upvote 0
I want to insert 6 rows between the negative and positive numbers and if it has 0's then insert the rows between 0 and positive numbers example
-5
-4
-3
6 spaces
1
2
3


or......
-5
-4
0
0
6 spaces
1
2
3

thank you for help...
 
Upvote 0
Something like this perhaps?
Code:
Sub InsertRowsDemo()
Dim LstRw&, iRng As Range, i As Range
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
If LstRw = 1 Then MsgBox ("No Data to work with."), , "No Data": Exit Sub
Set iRng = Range("A1:A" & LstRw)
Application.ScreenUpdating = False
For Each i In iRng
  If Not IsEmpty(i) Then
    If i < 1 And i(2) > 0 Then
      i(2).Resize(6).EntireRow.Insert
    End If
  End If
Next
Application.ScreenUpdating = True
End Sub

Hope it helps.
 
Upvote 0
Thanks it helps a little bit i think i can work with this to make it work for me it is inserting 6 rows between some of the zeros and some of the positive numbers

Thanks for help and the great start
 
Upvote 0
it is inserting 6 rows between some of the zeros and some of the positive numbers
Does this mean it's inserting between zero and zero, and between
some of the (consecutive) positive numbers?
It shouldn't be. :unsure:
It's told to test if a cell is less than zero AND if the next cell is greater than zero.

Are the values all whole numbers? - or will some of them be (say) 0.4?
0.4 would meet the criteria of "the next cell" being greater than zero while (depending on
formatting & such), it could be displaying a zero.
 
Upvote 0
OK. How about if you change this line:
If i < 1 And i(2) > 0 Then
to:
If i < 0.00000001 And i(2) > 0 Then

(Don't really have time to do exhaustive testing right now.
Will check back in the morning.)

Hope it helps.
 
Upvote 0
This code does the job

Sub InsertRowsDemo()
Dim lastrow, row
lastrow = Cells(Rows.Count, "A").End(xlUp).row
If lastrow = 3 Then MsgBox ("No Data to work with."), , "No Data": Exit Sub
'Set iRng = Range("P4:P" & LstRw)
Application.ScreenUpdating = False
Stop
Do Until Cells(lastrow, "P").Value < 0.5
lastrow = lastrow - 1
Loop

Stop
For row = lastrow To lastrow + 5 Step 1
Rows(row + 1).EntireRow.Insert
Next
Application.ScreenUpdating = True

'For row = lastrow To 4 Step -1
' If Not IsEmpty(row) Then
' If Cells(row, "P") > 0.5 Then Rows(row - 1).EntireRow.Insert
' End If
'Next
End Sub
 
Upvote 0
That last code doesn't (completely) work for me with the sample data you provided but
if it works on your real data that's all that counts.

Good job getting it figured out. :confused:
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
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