convert paste in multiple columns to single c

February 16, 2011 at 12:37:33
Specs: Windows XP
I have four colums in excel containing data cells and blank cells. Using a command button click event I wish to copy the data cells in the four columns (ABCD) into a specified range ("A18:A27") without blanks using VBA code.

If anyone can help with this please I will be most grateful

See More: convert paste in multiple columns to single c

Report •

February 18, 2011 at 21:50:58
It would help if we had a better idea of what the original lay out is.

It sounds like you only have 10 pieces of data, since you said you wanted it copied in a range of 10 cells (A18:A27).

Please clarify.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 19, 2011 at 02:19:11
Thank you for your reply.

It is only a small spreadsheet I am working on at the moment as I am trying to find my way about in VBA.

I have four ranges of data ("C2:C13") and then the same for columns EGI. There are text items in these ranges including duplicates and also blanks.

I am trying to set up a code which copies/pastes the data into the range ("A18:A27") without blanks and duplicates. Depending on the number of text items the range ("A18:A27") may not be full but I am trying to get the items listed from cell A18 downwards without blanks in between the pasted cells/duplicates.

I have tried a number of different scenarios and I have managed to copy and paste the items into column A from cell A18 downwards but I have been unable to remove the blanks (even though I am attempting to use Skip Blanks:=True and ActiveSheet.(Range"A18:A27).RemoveDuplicates)

Thanks again for your repy.

Report •

February 19, 2011 at 06:31:53
There are numerous ways to accomplish your task, including looping through the data and copying the strings, checking for duplicates before they are added to the Column A range.

Here's a totally different approach:

This code will no doubt need some tweaking but it seems to get the job done.

What it does is perform an Advanced Filter on your ranges (C, E, G & I) and create a long list in Column J. It then sorts the list in J to eliminate the blanks.

It then Filters that list over to Column A, bring over only Unique values.

You may have to change the location of the list ("J") if you are already using that column for something else.

I suggest that you try this in a backup copy of your workbook since it has to Clear some ranges in order to perform the filters.

Place the code in the VBA editor and size the window so that you can see your workbook behind it. Press F8 to single step through the code so that you can watch it create the filters and get a better understanding of how it works.

Option Explicit
Sub CopyUniques()
Dim nxtRw, lastRw As Integer
'Clear Column J and Destination Range so Filters won't fail
'Filter unique values from each range to next row in Column J
    Range("C2:C13").AdvancedFilter Action:=xlFilterCopy, _
                    CopyToRange:=Range("J1"), Unique:=True
      nxtRw = Range("J" & Rows.Count).End(xlUp).Row + 1
    Range("E2:E13").AdvancedFilter Action:=xlFilterCopy, _
                    CopyToRange:=Range("J" & nxtRw), Unique:=True
      nxtRw = Range("J" & Rows.Count).End(xlUp).Row + 1
    Range("G2:G13").AdvancedFilter Action:=xlFilterCopy, _
                    CopyToRange:=Range("J" & nxtRw), Unique:=True
      nxtRw = Range("J" & Rows.Count).End(xlUp).Row + 1
    Range("I2:I13").AdvancedFilter Action:=xlFilterCopy, _
                    CopyToRange:=Range("J" & nxtRw), Unique:=True
'Sort Column J to eliminate blanks
      Range("J:J").Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlGuess
'Filter unique value in Column J to A17
      lastRw = Range("J" & Rows.Count).End(xlUp).Row
      Range("J1:J" & lastRw).AdvancedFilter Action:=xlFilterCopy, _
                          CopyToRange:=Range("A17"), Unique:=True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

February 20, 2011 at 09:11:30
Thank you very much for your help with this.

Your code works perfectly, except for one thing which I have been trying to fathom out.

The data in cells G2:G13 is not being picked up. I cannot work out why. The other ranges are fine.

If I type data into unused cells - say G22:G33 - and then amend the range in the code it picks up the data from the new position. I have tried cutting and pasting the data back into G2:G13 and amending the code back but then when I run the code the range is not picked up again.

I shall keep trying to find a way round this.

As I am new to VBA I have a lot to learn but many thanks once again.

Report •

February 20, 2011 at 10:22:27
I can't think of any reason why G2:G13 would cause a problem, but obviously I don't know what is in your worksheet.

What do you mean by "The data in cells G2:G13 is not being picked up"?

Is the code throwing up an error, is it skipping the line when single stepping, etc.

What exactly is happening?

Have you tried a manual Advanced Filter on that range to see if there is something within Excel (as opposed to VBA) that is preventing the filter?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 21, 2011 at 08:05:18
Thank you again for your reply.

I had not thought of the advanced filter to check the ranges but as far as I can see there is nothing immediately apparent from this

I have run the procedure by pressing F8 and checking the status of the filter throughout. The ranges in the filter change from C to E to G to I as the procedure runs and then when the procedure has completed the range shown, if I go into the advanced filter and select the range for G2:G13, is J1:J5. But it is this for the other 3 ranges as well so I assume this is how the filter works and these ranges are then cleared when the procedure is next run.

Even though the procedure appears to select G2:G13 these cells are not copied and pasted but the other three ranges are.

I will keep looking but thanks again for your help

Report •

February 21, 2011 at 08:22:56
Without a copy of your workbook, there's not much else I can do.

If the workbook contains data of a non-confidential manner, I can supply an email address for you to send the workbook to. Other than that, I'm not sure that there is much more I can offer.

Seems strange that that one range won't filter.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 22, 2011 at 01:53:17
Thanks very much for all your help with this.

It has been very interesting and I have learned a lot.

I have managed to complete the task by using a combination of copy and paste special and your filters which is great

Report •

Ask Question