Results 1 to 12 of 12

Thread: [Excel] Cleanup 3 lines to one on Data Dump

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2014
    Posts
    6

    [Excel] Cleanup 3 lines to one on Data Dump

    Thanks in advance for your help.

    I am a complete nOOb, if you must ridicule me make it funny please.

    A report I use regularly has information for a single item on three (sometimes 4) lines in an excel spreadsheet with thousands of entries.

    So how do you cut line ?-1 ("?" meaning the referenced line of the spreadsheet, "?-1" is one line lower than the reference line, "?-2" is two lines below the reference line) from column X to column AG then paste it to line ? at column AH repeat the task with line ?-2 (paste at column AR) then delete lines ?-1 and ?-2 and move to the next line and repeat?

    What I think I need to do is insert a line of code to move to the next line in the sheet and loop until complete with the code already in the macro I have recorded but I can't figure out the required references.

    Code excerpt here:

    Range("V1:AE1").Select
    Selection.Copy
    Range("AG1").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    Range("AQ1").Select
    ActiveSheet.Paste
    Range("V3:AE3").Select
    Application.CutCopyMode = False
    Selection.Cut
    Range("AG2").Select
    ActiveSheet.Paste
    Range("V4:AE4").Select
    Selection.Cut
    ActiveWindow.SmallScroll ToRight:=5
    Range("AQ2").Select
    ActiveSheet.Paste
    Rows("3:4").Select
    Range("W3").Activate
    Selection.Delete Shift:=xlUp
    ActiveWindow.SmallScroll ToRight:=-4
    LOOP HERE
    Range("V4:AE4").Select
    Selection.Cut
    Range("AG3").Select
    ActiveSheet.Paste
    Range("V5:AE5").Select
    Selection.Cut
    Range("AQ3").Select
    ActiveSheet.Paste
    Rows("4:5").Select
    Range("S4").Activate
    Selection.Delete Shift:=xlUp
    TO HERE
    Loop Until IsEmpty(V)

    End Sub

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: [Excel] Cleanup 3 lines to one on Data Dump

    Will put something together on Monday

  3. #3
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: [Excel] Cleanup 3 lines to one on Data Dump

    Assuming the "item" is in column A, and that the columns have headers, try something like this:

    Code:
    Sub copyDupes()
        Dim ws As Worksheet
        Dim lr As Long
        Dim item As String
        Dim numItems As Integer
        Dim j As Long
        Dim k As Integer
        Dim copyCol As Integer
        
        Set ws = ActiveSheet
        lr = ws.Range("a" & Rows.Count).End(xlUp).Row
        
        Application.ScreenUpdating = False
        
        With ws
            For j = 2 To lr
                item = .Range("a" & j).Value
                numItems = 1
                k = 1
                
                While .Range("a" & j + k).Value = item
                    numItems = numItems + 1
                    k = k + 1
                Wend
                If numItems > 1 Then
                    For k = 1 To numItems - 1
                        .Range("x" & j + k & ":ag" & j + k).Copy
                        copyCol = .Cells(j, Columns.Count).End(xlToLeft).Column + 1
                        .Cells(j, copyCol).PasteSpecial
                        Application.CutCopyMode = False
                        .Cells(j + k, 1) = "aaaaa"
                    Next k
                End If
                j = j + numItems - 1
            Next j
            
            For j = lr To 3 Step -1
                If .Range("a" & j).Value = "aaaaa" Then
                    .Range("a" & j).EntireRow.Delete
                End If
            Next j
        End With
        
        Set ws = Nothing
        Application.ScreenUpdating = True
    End Sub

  4. #4

    Thread Starter
    New Member
    Join Date
    Aug 2014
    Posts
    6

    Re: [Excel] Cleanup 3 lines to one on Data Dump

    That code (ref below) cuts out the monthly task and adds it to the end of the weekly task which is one row below where it needs to be.

    That is almost what I need.

    I thought I would be able to follow the code once I saw it work, but I am still mystified as to what the commands are doing.

    That being said, where can I send you a beer?

    A few references that I hope will help in the next (final?) revision:
    There is a reference number in column A of the row I want to be the single row of data.
    The rows of data below this line have no information in column A.
    The lines that need to be moved then deleted go from column X to column AG.
    The values for these lines in column X are "Weekly" (paste AH to AQ) , "Monthly" (Paste AR to BA) and "Re-Open" (Paste BB to BK)
    Last edited by SmidgenPidgeon; Aug 25th, 2014 at 09:42 AM.

  5. #5
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: [Excel] Cleanup 3 lines to one on Data Dump

    So, first off, instead of looking for repeating "reference numbers" in successive rows (column A), we need to look for blank cells in column A below the cell that has the reference number? Will there be data in the rows below in the other columns, or will there be an entire blank row in between two different reference numbers?

    Can you zip and attach some sample data (if not too sensitive)?

  6. #6

    Thread Starter
    New Member
    Join Date
    Aug 2014
    Posts
    6

    Re: [Excel] Cleanup 3 lines to one on Data Dump

    Working on it...

  7. #7

    Thread Starter
    New Member
    Join Date
    Aug 2014
    Posts
    6

    Re: [Excel] Cleanup 3 lines to one on Data Dump


  8. #8
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: [Excel] Cleanup 3 lines to one on Data Dump

    You have merged cells in there, which is part of the issue with not writing to the correct line. Can you do without the merged cells?

  9. #9

    Thread Starter
    New Member
    Join Date
    Aug 2014
    Posts
    6

    Re: [Excel] Cleanup 3 lines to one on Data Dump

    Yes, I forgot to unmerge them.

  10. #10
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: [Excel] Cleanup 3 lines to one on Data Dump

    Make sure your data is "clean" (no merges, no blank rows, etc.), and try something like this (I'll explain any part you need):

    Code:
    Sub copyData()
        Dim ws As Worksheet
        Dim lr As Long
        Dim numItems As Integer
        Dim j As Long
        Dim k As Integer
        Dim copyCol As String
        
        Set ws = ActiveSheet
        lr = ws.Range("a" & Rows.Count).End(xlUp).Row
        
        Application.ScreenUpdating = False
        
        With ws
            For j = 2 To lr
                If j <> lr Then
                    numItems = 1
                    k = 1
                    
                    While IsEmpty(.Range("a" & j + k))
                        numItems = numItems + 1
                        k = k + 1
                    Wend
                    If numItems > 1 Then
                        For k = 1 To numItems - 1
                            .Range("x" & j + k & ":ag" & j + k).Copy
                            Select Case k
                                Case Is = 1
                                    copyCol = "ah"
                                Case Is = 2
                                    copyCol = "ar"
                                Case Is = 3
                                    copyCol = "bb"
                                Case Else
                                    MsgBox "Have not allowed for more than 4 lines per reference yet."
                                    Application.ScreenUpdating = True
                            End Select
                            .Range(copyCol & j).PasteSpecial
                            Application.CutCopyMode = False
                        Next k
                    End If
                    j = j + numItems - 1
                Else
                    numItems = .Range("x" & Rows.Count).End(xlUp).Row - j + 1
                    If numItems > 1 Then
                        For k = 1 To numItems - 1
                            .Range("x" & j + k & ":ag" & j + k).Copy
                            Select Case k
                                Case Is = 1
                                    copyCol = "ah"
                                Case Is = 2
                                    copyCol = "ar"
                                Case Is = 3
                                    copyCol = "bb"
                                Case Else
                                    MsgBox "Have not allowed for more than 4 lines per reference yet."
                                    Application.ScreenUpdating = True
                            End Select
                            .Range(copyCol & j).PasteSpecial
                            Application.CutCopyMode = False
                        Next k
                    End If
                End If
            Next j
            lr = .Range("x" & Rows.Count).End(xlUp).Row
            
            For j = lr To 3 Step -1
                If IsEmpty(.Range("a" & j)) Then
                    .Range("a" & j).EntireRow.Delete
                End If
            Next j
        End With
        
        Set ws = Nothing
        Application.ScreenUpdating = True
    End Sub

  11. #11

    Thread Starter
    New Member
    Join Date
    Aug 2014
    Posts
    6

    Re: [Excel] Cleanup 3 lines to one on Data Dump

    Thank you so very much! What can I do for you?

  12. #12
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: [Excel] Cleanup 3 lines to one on Data Dump

    just enjoy!

Posting Permissions

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



Click Here to Expand Forum to Full Width