I have a spreadsheet with a column with positive and negative numbers. How can I separate them into 2 separate columns?
I have a spreadsheet with a column with rows of positive and negative numbers in no set order. I want to move all of the negative numbers to an adjacent column so that I end up with two columns, one with positive numbers in all of the rows and one with negative numbers in all of the rows. How can I do this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i just modified it a bit, it now deletes all the blank cells in columns B and C and gives only the values...
If you needed the values to stay in the same row, than this will be of no value to you, but if you just needed the values, this is a much cleaner solution than the previous.
Sub PositiveAndNegative()
Dim LastRow As Long, MyRange As Range, i As Integer, j As Integer
Range("A1").Select
Selection.End(xlDown).Sele
LastRow = ActiveCell.Row
For j = 1 To LastRow
If Range("A" & j).Value > 0 Then
Range("B" & j).Value = Range("A" & j).Value
End If
Next
For i = 1 To LastRow
If Range("A" & i).Value < 0 Then
Range("C" & i).Value = Range("A" & i).Value
End If
Next
Rng = "B1:C" & LastRow
Range(Rng).Select
Selection.SpecialCells(xlC
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub
Good luck...
PosAndNeg.xlsm