-
Aug 22nd, 2014, 02:53 PM
#1
Thread Starter
New Member
[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
-
Aug 24th, 2014, 06:27 PM
#2
Re: [Excel] Cleanup 3 lines to one on Data Dump
Will put something together on Monday
-
Aug 24th, 2014, 07:15 PM
#3
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
-
Aug 25th, 2014, 09:31 AM
#4
Thread Starter
New Member
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.
-
Aug 25th, 2014, 09:37 AM
#5
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)?
-
Aug 25th, 2014, 09:46 AM
#6
Thread Starter
New Member
Re: [Excel] Cleanup 3 lines to one on Data Dump
-
Aug 25th, 2014, 10:01 AM
#7
Thread Starter
New Member
Re: [Excel] Cleanup 3 lines to one on Data Dump
-
Aug 25th, 2014, 10:22 AM
#8
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?
-
Aug 25th, 2014, 10:23 AM
#9
Thread Starter
New Member
Re: [Excel] Cleanup 3 lines to one on Data Dump
Yes, I forgot to unmerge them.
-
Aug 25th, 2014, 10:46 AM
#10
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
-
Aug 25th, 2014, 11:00 AM
#11
Thread Starter
New Member
Re: [Excel] Cleanup 3 lines to one on Data Dump
Thank you so very much! What can I do for you?
-
Aug 25th, 2014, 11:01 AM
#12
Re: [Excel] Cleanup 3 lines to one on Data Dump
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|