Copy & Paste Columns to Specific Range while Removing Blanks & Duplicates

[dondada82]

I'm creating a document in excel that needs specific information copied from columns and pasted ntot a specific range. The problem I'm having is that it's copying blanks and duplicate information from the cells and it's putting it outside my range. I would like to remove the blanks and duplicate values and keep all information within my "Panels" range. My "Panels" range is from AZ95:BM104 respectively. Here's the code I currently have.

Sub Marco1 ()
     Range("S2:AV100").Select       'Selection range that I'm copying is S2:AV100
     Selection.Copy
     Range ("Panels").Select           'Range pasting to is panels
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _ 
          False, Transpose:=True           'Special Paste values only
     Application.CutCopyMode = False

     Activesheet.Range("Panels").RemoveDuplicates Column:=Array(1,2), Header:=xlNo    'Remove all duplicate values this doesn't work

End Sub

Any help is greatly appreciated.

[Sintek]

Hi dondada82

In future please view the forum rules before posting. Your post does not comply with rule #3.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window.

Try this: Not tested.

Option Explicit
Sub Marco1()
Dim i As Integer
Dim nextRow As Long
nextRow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1
For i = 2 To 100
   If Sheet1.Cells(i, 19).Value <> "" Then
      Sheet1.Cells(i, 19).EntireRow.Copy Destination:=Sheet2.Range("AZ95" & nextRow)
      nextRow = nextRow + 1
   End If
Next i
End Sub

[dondada82]

Sintek,

I apologize for not following the forum rules. I'm new to this and will follow the rules going forward. I modified the coding that you've provided and I'm getting an error. Here's my error.

Run-time error '1004':
The information cannot be pasted because the copy area and the paste area are not the same size and shape. Try one of the following:

Click a single cell and then paste.
Select a rectangle that is the same size and shape, and then paste.

Here's how I modified the coding:

Option Explicit
Sub Marco1()
Dim i As Integer
Dim nextRow As Long
Dim ws As Worksheet
Set ws = Sheets("Folder Generator")
nextRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
For i = 2 To 100
   If ws.Cells(i, 19).Value <> "" Then
      ws.Cells(i, 19).EntireRow.Copy Destination:=ws.Range("AZ95" & nextRow) 'Getting an error on this line
      nextRow = nextRow + 1
   End If
Next i
End Sub

[Sintek]

Hi dondada2

Please edit your posts to conform to the rules.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window.

[dondada82]

Sintek,

Sorry about that. It's a learning curve for me. They've been edited.

[Sintek]

The selection S2:AV100 you are copying from and AZ95:BM104 pasting to is not same size...Perhaps you should upload a sample workbook so that we can have a look.

[Sintek]

On This line

 Destination:=ws.Range("AZ95" & nextRow) 'Getting an error on this line

ws refers to your active worksheet...What is the name of the worksheet you are sending info to.

[dondada82]

the name of the worksheet is Folder Generator

All information is on the same worksheet. There's values from S:AV that needs to be copied and pasted to another section, which I have set a range called Panels. The problem with my original code was that:

1) the paste went beyond the specified range "Panels". For example it pasted into the Panels range and one column to the right. Then it pasted many blank rows below and outside of the "Panels" range. I would like the information to be contained in the "Panels" range, which is AZ95:BM104 respectively.
2) It contained duplicated values, which I would like to remove, if possible.

[jindon]

Sub Marco1 ()
     Range("S2:AV100").Select       'Selection range that I'm copying is S2:AV100
     Selection.Copy
     Range ("Panels").Select           'Range pasting to is panels
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _ 
          False, Transpose:=True           'Special Paste values only
     Application.CutCopyMode = False

     Activesheet.Range("Panels").RemoveDuplicates Column:=Array(1,2), Header:=xlNo    'Remove all duplicate values this doesn't work

End Sub

Your code is confusing, better upload a small sample workbook with before/after.

[dondada82]

for some odd reason the attachments link in this forum isn't working.

This is what I'm trying to do:

I have a worksheet (Folder Generator) that I created in excel that gets populated from other worksheets within the document. At any given moment when I up the the Folder Generator with new information columns S:AV information can change, but the values within these columns will never be full and many blanks will be portrayed. Some of the values within these column can also be duplicated, which I would like to have it delete all duplicated values (but I need at least one of the duplicated values to stay).

My original code copied and special pasted values only from S2:AV100 (values will never grow more than that range) into the "Panels" range, which starts at $AZ$95 and ends at $BM$104.

The copy and paste feature works, but its pasting some of the information outside of the "Panels" range and filling all empty rows with blanks. I know this because I have borders that disappear as soon as I update the values in the "Panels" range.

I hope my explanation is straight forward and doesn't add more confusion. I do apologize in advance.

[jindon]

I think some information is still missing about "many blanks", "duplicates" etc and how you want the result.

to upload a workbook,

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[dondada82]

The file should be here now. Thanks again for all your help.

[jindon]

Thanks for the workook.

I can see many blanks, but no duplicates...

You have 30 columns 100 rows to copy, S2:AV100, and only 10 rows 14 columns in "Panels" named range.
How do you want the result?

Can you upload a sample data and the result clearly showing the logic behind it?

[dondada82]

If you populate the folder with engine 1 inspection (A2) and hit the update package button, then hit the update panels button (with my original coding) it inputs the information in the "Panels" range as first row 432AT, 432AT, 432AT, etc...... and second row as 443AB, 443AB, 443AB, etc.....

I would like it to remove the duplicates and just see one set of 432AT and 433AB and no blank cells.

[jindon]

Still not clear to me though...

Sub test()
    Dim a, i As Long, ii As Long, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    a = Range("S2:AV100").Value
    With Range("Panels")
        .ClearContents
        For i = 1 To UBound(a, 1)
            For ii = 1 To UBound(a, 2)
                If (a(i, ii) <> "") * (a(i, ii) <> 0) * (Not dic.exists(a(i, ii))) Then
                        dic(a(i, ii)) = Empty
                        .Cells(dic.Count).Value = a(i, ii)
                End If
            Next
        Next
    End With
End Sub

[dondada82]

Jindon,

That looks like it's working as it should. Many thanks.

[jindon]

Glad that was what you wanted and thanks for the rep.

If you want to stop when data to be pasted exceeds the number of cells in "Panels" then add 2 lines.

Sub test()
    Dim a, i As Long, ii As Long, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    a = Range("S2:AV100").Value
    With Range("Panels")
        .ClearContents
        For i = 1 To UBound(a, 1)
            For ii = 1 To UBound(a, 2)
                If (a(i, ii) <> "") * (a(i, ii) <> 0) * (Not dic.exists(a(i, ii))) Then
                    dic(a(i, ii)) = Empty
                    .Cells(dic.Count).Value = a(i, ii)
                    If dic.Count = .Count Then Exit For
                End If
            Next
            If dic.Count = .Count Then Exit For
        Next
    End With
End Sub

[dondada82]

Jindon,

Thanks again. I went to get some sleep since it after 6am my time. So everything works perfectly. I filled some of the columns with erroneous data to see if it stayed within the "Panels" range and it did. I'll add the two lines above, although I may never need it. Thanks again!