Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Oklahoma City, Oklahoma, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Moving Boxes with code (E2kSR1)

    I have a report that comes from an Oracle database. The database can only export the report to the printer or to HTML if needed to be saved.

    I open it in Excel, and convert the Merge and Center to place the data in it's own cell. What I get is a report with spaces between the rows, one or two usually, and then a row of data in columns c,f,j,m,q,and v. I delete the blank columns and move everything to a-f. So far so good. The problem is that some of the rows will have data in a, blank in b and c, data in d,e and f. Immediately above the b and c row is the data that should be in the current row. For example; A10(Doe, John), B10(Blank), C10(Blank), D10(SSAN), E10(Date of Birth), F10(Master ID), but in the row above B9(Location), C9(Date of Arrest) [this is a jail after all].

    What I need is a way to have the computer look at column A and if it is blank cycle to the next Row. If not blank, look at column B, if if is blank move the row above down along with the row from column C next to it. (For some reason it is always these two columns together that are off) The data in the rows above need to be moved without affecting all the other data around it (cut and paste), if the column B is not empty then cycle also.

    Can this be done. Now I and going through and doing the moves by hand and it is taking over a half hour each time.

    Help Please

    Jail Admin Medical (JAM) <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Boxes with code (E2kSR1)

    Could you possibly post a Workbook with a sheet that shows what it original looks like and a second sheet that show what you want it to look like? It is a little hard to figure out what you want from a description.
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving Boxes with code (E2kSR1)

    Hi Jam! I sort of understand what you need, but if you could reply with a sample spreadsheet attached(messy on one worksheet, fixed on another), this would really help. Be sure to change the names: we don't want you doing time! Also make each field have a different value, so I can see where things get moved to.

    The solution will have to be a macro. Is that OK? --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Star Lounger
    Join Date
    Apr 2001
    Location
    Oklahoma City, Oklahoma, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Boxes with code (E2kSR1)

    Here is the example. I need to move the cells down without messing up the formatting. The real spreadsheet goes for 2500 names and is a bear to do manually.


    JAM
    Attached Files Attached Files

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Boxes with code (E2kSR1)

    I think that the VBA routine below will do what you want if I understand your sample correctly.

    <pre>Public Sub DelEmptyCells()
    Dim lLastRow As Long, lLastCol As Long, I As Long, J As Long
    Dim oCell As Range
    While Worksheets("Sheet1").Range("A1").Value = ""
    Worksheets("Sheet1").Range("A1").EntireRow.Delete
    Wend
    lLastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    lLastCol = Worksheets("Sheet1").Range("IV1").End(xlToLeft).Co lumn - 1
    For I = lLastRow To 0 Step -1
    For J = lLastCol To 0 Step -1
    If Worksheets("Sheet1").Range("A1").Offset(I, J).Value = "" Then
    Worksheets("Sheet1").Range("A1").Offset(I, J).Delete (xlShiftUp)
    End If
    Next J
    Next I
    End Sub
    </pre>

    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Apr 2001
    Location
    Oklahoma City, Oklahoma, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Boxes with code (E2kSR1)

    YES!!
    Thank You so very much. It works great as is. I will try this on the data at the jail tomorrow. After backing it up of course.

    JAM

Posting Permissions

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