Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Is it possible to do something that looks in a column that should be empty and if it finds data it moves it to the next column over? For example, Column B does not have data and Column C has data. If Column B has data (which it should not) I want it moved to column C and deleted from Column B.
    I' new at this so any help you can give would be appreciated.
    thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='CCJ' post='764505' date='10-Mar-2009 00:49']Is it possible to do something that looks in a column that should be empty and if it finds data it moves it to the next column over? For example, Column B does not have data and Column C has data. If Column B has data (which it should not) I want it moved to column C and deleted from Column B.
    I' new at this so any help you can give would be appreciated.
    thanks[/quote]
    Should just cells in column B be moved, or all cells to the right of them too?

  3. #3
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='764513' date='09-Mar-2009 17:14']Should just cells in column B be moved, or all cells to the right of them too?[/quote]
    Each cell needs to move to the right and be deleted from the original location.

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='CCJ' post='764633' date='10-Mar-2009 16:23']Each cell needs to move to the right and be deleted from the original location.[/quote]
    What should happen to anything that was previously in the cell to the right?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='CCJ' post='764633' date='10-Mar-2009 17:23']Each cell needs to move to the right and be deleted from the original location.[/quote]
    Does this short macro do what you want? (I assumed that column C will be empty if B is filled):
    Code:
    Sub MoveB2C()
      Dim r As Long
      Dim m As Long
      m = Cells(Rows.Count, 2).End(xlUp).Row
      For r = 1 To m
    	If Not Cells(r, 2) = "" Then
    	  Cells(r, 2).Cut Destination:=Cells(r, 3)
    	End If
      Next r
    End Sub

  6. #6
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='764637' date='10-Mar-2009 09:34']Does this short macro do what you want? (I assumed that column C will be empty if B is filled):
    Code:
    Sub MoveB2C()
      Dim r As Long
      Dim m As Long
      m = Cells(Rows.Count, 2).End(xlUp).Row
      For r = 1 To m
    [tab][/tab]If Not Cells(r, 2) = "" Then
    [tab][/tab]  Cells(r, 2).Cut Destination:=Cells(r, 3)
    [tab][/tab]End If
      Next r
    End Sub
    [/quote]


    I think I did not make myself clear enough - this macro is great however I want it to do the same thing for the entire spreadsheet. For example for cols b/c, d/e, f/g, etc.
    Also I have just a few at the end of the file that the data needs to move left one field instead of right.
    Hope you can help
    thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In what circumstances should data move to the left?

    Wouldn't it be better to ask for .csv files that are aligned correctly?

  8. #8
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='764906' date='11-Mar-2009 12:22']In what circumstances should data move to the left?

    Wouldn't it be better to ask for .csv files that are aligned correctly?[/quote]


    I agree with getting the .csv file correctly aligned however this file is spit out of a program used by one large company and they will not check into the problem.....I tried that first.

    I checked again and I was mistaken - no data needs to move to the left....but all the columns (maybe 40) need to be checked and moved to the right.
    Thanks again for your help.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    So after all corrections, columns B, D, F etc. will be empty?

  10. #10
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='764908' date='11-Mar-2009 12:30']So after all corrections, columns B, D, F etc. will be empty?[/quote]

    yes

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this version:
    Code:
    Sub MoveRight()
      Dim r As Long
      Dim m As Long
      Dim c As Long
      Dim n As Long
      m = Cells.Find(What:="*", SearchOrder:=xlByRows, _
    	SearchDirection:=xlPrevious).Row
      n = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
    	SearchDirection:=xlPrevious).Column
      For c = 2 To n Step 2
    	For r = 1 To m
    	  If Not Cells(r, c) = "" Then
    		Cells(r, c).Cut Destination:=Cells(r, c + 1)
    	  End If
    	Next r
      Next c
    End Sub

  12. #12
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='764911' date='11-Mar-2009 12:39']Try this version:
    Code:
    Sub MoveRight()
      Dim r As Long
      Dim m As Long
      Dim c As Long
      Dim n As Long
      m = Cells.Find(What:="*", SearchOrder:=xlByRows, _
    [tab][/tab]SearchDirection:=xlPrevious).Row
      n = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
    [tab][/tab]SearchDirection:=xlPrevious).Column
      For c = 2 To m Step 2
    [tab][/tab]For r = 1 To m
    [tab][/tab]  If Not Cells(r, c) = "" Then
    [tab][/tab][tab][/tab]Cells(r, c).Cut Destination:=Cells(r, c + 1)
    [tab][/tab]  End If
    [tab][/tab]Next r
      Next c
    End Sub
    [/quote]


    Definitely a real step forward. Slight problem. I am attaching the file I am using to see if that helps. I have highlighted the lines that are examples of the gliches - seems to be the same type of error in different locations.
    Thanks ever so much for your help - it is going to save us loads of time.
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The columns don't form a regular pattern so it's not clear what should happen - for example, columns G and H (adjacent) are filled, then J, L, N, etc. until T (jumps of 2), then column W (a jump of 3), then Y and AA (jumps of 2 again).

    YOU will have to indicate EXACTLY what you want.

  14. #14
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='764954' date='11-Mar-2009 16:33']The columns don't form a regular pattern so it's not clear what should happen - for example, columns G and H (adjacent) are filled, then J, L, N, etc. until T (jumps of 2), then column W (a jump of 3), then Y and AA (jumps of 2 again).

    YOU will have to indicate EXACTLY what you want.[/quote]

    As I see more that there are no real patterns here, I opened one of these .csv files in a text editor. What I find is that there are sometimes 3 commas between fields, mostly 2 commas and sometimes 1 between fields. This is my problem and where the columns are going awry.
    I'm wondering if there is some sort of script or way to parse the fields so they all have the same number of commas between the fields. That would solve the whole problem.
    Is that possible?
    thanks

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    That's basically the same problem - someone (YOU) will have to decide how many commas there should be.

Page 1 of 2 12 LastLast

Posting Permissions

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