Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cut/Paste Help (2003 sp2)

    greetings!
    I am sure this has been discussed, but I could not find exactly what I need.

    I have 30K+ line file, that when generated has every so often down through the file the following condition:

    Row 1 thru n filled, next row, Cell A(n)blank. Cell B(n) next to blank cell contains info needed in blank cell A(n).

    need to move data in cell B(n) into cell A(n) and continue until end of file.

    any ideas?

    Thanks,
    Brad

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Cut/Paste Help (2003 sp2)

    Brad:

    Simple way without Macro would be
    1. go to Cell A1
    2. From the Edit menu choose the Go To command and click the Special Button
    3. In the Go To Special dialog box select Current Region and click OK
    4. For a second time from the Edit menu choose the Go To command and click the Special Button
    5. In the Go To Special dialog box select Blanks and click OK
    6. Excel will select all blank cells in the region and make one cell the active cell
    7. In the active cell type = and press the right arrow key (Excel understands this to mean for blank cells get the data in the cell right of it
    8. Hold Down the CTR key and press Enter. This will fill all the blank cells in Col A with the Values in Col B

    Good luck

    Tom Duthie

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cut/Paste Help (2003 sp2)

    You could run a macro:
    <code>
    Sub RemoveEmpties()
    Dim r As Long
    Dim n As Long
    n = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    For r = 1 to n
    If Range("A" & r) = "" Then
    Range("A" & r).Delete Shift:=xlShiftToLeft
    End If
    Next r
    End Sub</code>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cut/Paste Help (2003 sp2)

    Or perhaps closer to what Brad asked:
    - Select column A.
    - Select Edit | Go To...
    - Click Special...
    - Select Blanks, then click OK.
    - Select Edit | Delete...
    - Specify that you want to shift cells to the left, then click OK.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cut/Paste Help (2003 sp2)

    Good suggestions, but I have data in cells c thru xxxxx that need to remain in those cells.
    If I read the macros correctly, it will shift the cells. This is not exactly what is needed.

    I simply need to locate the blank cell(s) in col A and move the information in col B right next to the blank cell into the blank cell.


    Brad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cut/Paste Help (2003 sp2)

    Change

    Range("A" & r).Delete Shift:=xlShiftToLeft

    to

    Range("B" & r).Cut Destination:=Range("A" & r)

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cut/Paste Help (2003 sp2)

    Absolutly Cool!


    Thanks,
    Brad

Posting Permissions

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