0

This is what I'm trying to get the data to look like. I am trying to match the values on the right with the top row of the values on the left. Sometimes there is only one row of information, and sometimes there are multiple rows. I've figured the below, which copies and moves to the correct location, but I cannot paste the values, also how would I get excel to loop through to continue the correct procedures? There are 20,000 lines of data in the sheet.

Option Explicit

Sub PA31_Macro()

  ActiveCell.Select
  Selection.End(xlDown).Select
  ActiveCell.Offset(1, 0).Select

If IsEmpty(ActiveCell) Then
  ActiveCell.Offset(-1, 0).Select
  Range(Selection, Selection.End(xlToRight)).Select
  Selection.Cut
  ActiveCell.Offset(-2, -1).Select

  If IsEmpty(ActiveCell) Then
    ActiveCell.Offset(1, 1).PasteSpecial xlPasteValues        
  Else
    ActiveCell.Offset(1, 0).Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.PasteSpecial xlPasteValues        
  End If
      
Else    
  ActiveCell.Offset(-1, 0).Select
  ActiveCell.Select
  Range(Selection, Selection.End(xlDown)).Select
  Range(Selection, Selection.End(xlToRight)).Select
  Selection.Cut
  ActiveCell.Offset(-2, -1).Select

  If IsEmpty(ActiveCell) Then
    ActiveCell.Offset(1, 1).PasteSpecial xlPasteValues
  Else
   ActiveCell.Offset(1, 0).Select
   Selection.End(xlUp).Select
   ActiveCell.Offset(0, 1).Select
   Range(ActiveCell).PasteSpecial xlPasteValues
  End If  

End If

End Sub

Data i am trying to edit

Data i am trying to edit

daftb0y
  • 1
  • 1
  • Does "the top row of the values on the left" mean 'the top row of each block of contiguous rows on the left"? Does "match" mean that you want to find a row with the same data in the block to the right of and down from the left-side block? Where should the values be pasted to? This would be easier to understand if you gave an example of what the result should look like. Also, if you pasted your data as text instead of a screenshot to allow others to follow/answer. – stifin Mar 10 '21 at 16:10
  • Could you post an image of the desired result? Include data with and without the right part. It kind of seems logical that the data not containing the right part will be transferred as is, and the other data will not have the row that doesn't contain the right part. Please do confirm or clarify. Also, clarify if the result should be pasted into a new worksheet or just overwrite. Then clarify if the data are values or formulas. There is also this unclear part where it changes from `068-01600` to `068-02000` where it doesn't contain the left part. What should be done in this case? – VBasic2008 Mar 10 '21 at 16:12
  • 1
    In addition to providing information as to what you want for results, I suggest you carefully read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and edit your code to remove all those `Select`'s and `ActiveCell`'s in favor of fully qualified variables. – Ron Rosenfeld Mar 10 '21 at 17:50
  • The top row of the values on the left does mean the top row of each block of contiguous rows on the left. Match means I want to move the block of contiguous rows on the right to the same row as the top on the left. So that the top of each side is on the same row. Understood on making the data as text, will do that in the future. – daftb0y Mar 11 '21 at 17:24
  • I just posted a picture of what I want the data to look like. I would like the data to be overwritten, not posted to a new worksheet. the data is values. If the numbers change that should not be treated differently, it is an alternate PN. I am trying to just move the data relative to the locations they are in. – daftb0y Mar 11 '21 at 17:29

0 Answers0