Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Inserting Blank Cells (2000)

    I have a worksheet with 2 columns of numbers in ascending order, but the columns are not identical. For example, Column A may have the following numbers in Rows 1-3: 100, 110, and 120, while the corresponding cells in Ccolumn B might contain: 102, 103, and 114. What I would like to do is be able to insert blank cells between, in this example rows 1 and 2 in Column A, and then "transfer" the numbers that were originally in Column B, Rows 1 and 2 (i. e., the two numbers that are between the first two numeric entries in ColumnA) to cells that correspond to the blank cells inserted in Column A.
    Any help?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Blank Cells (2000)

    Put the "new" numbers at the end of the row with the numbers, then do a sort using data, sort, options, Sort left to right.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Blank Cells (2000)

    Jan,
    Not following you.
    Jeff

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inserting Blank Cells (2000)

    Move column 2 numbers to the bottom of column 1.
    Now they are all in the same column though not sorted.
    Sort column 1 and they will be in the correct order

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Blank Cells (2000)

    Sorry, misread your description. Just do as Steve suggests.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Blank Cells (2000)

    Close, but I need to keep the numbers that were originally in Column B in Column B.. That is, I would like the result in my example to reflect: the cell at Column A, Row 1, would contain 100, the cell at Column A, Row 4 would contain 110, and the cell at Column A, Row 6 would contain 120, Column B, Row 2 would contain 102, Column B, Row 3 would contain 103, and Column B, Row 5 would contain 114.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inserting Blank Cells (2000)

    Misintepreted what you wanted. Try this routine.
    Select the range of 2 columns, then run the macro. It will overwrite the 2 columns (it will go past the original range). so make sure you do it on a copy of the sheet otherwise you will lose the original. It only affects the 2 columns selected. it does nothing to any other columns.

    <pre>Option Explicit
    Sub JlKirk()
    Dim wksOri As Worksheet
    Dim rng As Range
    Dim wks As Worksheet
    Dim rCol1 As Range
    Dim rCol2 As Range

    Set wksOri = ActiveSheet
    Set rCol1 = Selection.Columns(1)
    Set rCol2 = Selection.Columns(2)
    Set wks = Worksheets.Add

    With wks
    .Range("a1") = "a"
    rCol1.Copy .Range("A2")
    rCol2.Copy .Range("a65536").End(xlUp).Offset(1, 0)

    .Range(.Range("a1"), .Range("a65536").End(xlUp)). _
    AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=.Range("C1"), Unique:=True

    .Range("C1").Sort Key1:=.Range("c1"), Order1:=xlAscending, _
    Header:=xlYes, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom

    .Range("D2").Formula = "=VLOOKUP(C2, '" & _
    wksOri.Name & "'!" & rCol1.Address & ",1,0)"
    .Range("e2").Formula = "=VLOOKUP(C2, '" & _
    wksOri.Name & "'!" & rCol2.Address & ",1,0)"

    Set rng = .Range(.Range("D2"), .Range("c65536").End(xlUp).Offset(0, 2))

    .Range("D2:E2").AutoFill Destination:=rng
    rng.SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
    rng.Copy
    rCol1.Cells(1).PasteSpecial xlPasteValues
    Application.DisplayAlerts = False
    .Delete
    Application.DisplayAlerts = True
    End With
    wksOri.Select
    Set rng = Nothing
    Set wks = Nothing
    Set rCol1 = Nothing
    Set rCol2 = Nothing
    Set wksOri = Nothing
    End Sub</pre>


    Steve

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Inserting Blank Cells (2000)

    As usual, works great, Steve! Many thanks.

    Now, one further (and I promise, no more) modification. In actuality, each of the "numeric cells" in Columns A and B have "text" (may be numeric or pure text) cells immediately adjacent to them that describe the number in the adjacent cell. I have attached a file depicting this using my original example-see Sheet 1. What I want to do is accomplish the same result as before, but allowing for the "text" ofr descriptive cells to "tag" along with their respective counterpart. Sheet 2 in the attached shows what I want to accomplish. Any help?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inserting Blank Cells (2000)

    You really need to try to explain what you have. This is a relatively simple mod, others might take more effort.
    Try this

    <pre>Sub JlKirk2()
    Dim wksOri As Worksheet
    Dim rng As Range
    Dim wks As Worksheet
    Dim rCol1 As Range
    Dim rCol2 As Range

    Set wksOri = ActiveSheet
    Set rCol1 = Selection.Columns(1)
    Set rCol2 = Selection.Columns(3)
    Set wks = Worksheets.Add

    With wks
    .Range("a1") = "a"
    rCol1.Copy .Range("A2")
    rCol2.Copy .Range("a65536").End(xlUp).Offset(1, 0)

    .Range(.Range("a1"), .Range("a65536").End(xlUp)). _
    AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=.Range("C1"), Unique:=True

    .Range("C1").Sort Key1:=.Range("c1"), Order1:=xlAscending, _
    Header:=xlYes, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom

    .Range("D2").Formula = "=VLOOKUP(C2, '" & _
    wksOri.Name & "'!" & rCol1.Resize(, 2).Address & ",1,0)"
    .Range("e2").Formula = "=VLOOKUP(C2, '" & _
    wksOri.Name & "'!" & rCol1.Resize(, 2).Address & ",2,0)"
    .Range("f2").Formula = "=VLOOKUP(C2, '" & _
    wksOri.Name & "'!" & rCol2.Resize(, 2).Address & ",1,0)"
    .Range("g2").Formula = "=VLOOKUP(C2, '" & _
    wksOri.Name & "'!" & rCol2.Resize(, 2).Address & ",2,0)"

    Set rng = .Range(.Range("D2"), .Range("c65536").End(xlUp).Offset(0, 4))

    .Range("D2:g2").AutoFill Destination:=rng
    rng.SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
    rng.Copy
    rCol1.Cells(1).PasteSpecial xlPasteValues
    Application.DisplayAlerts = False
    .Delete
    Application.DisplayAlerts = True
    End With
    wksOri.Select
    Set rng = Nothing
    Set wks = Nothing
    Set rCol1 = Nothing
    Set rCol2 = Nothing
    Set wksOri = Nothing
    End Sub</pre>


    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •