Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: copy 4 columns into one, missing empty or #VALUE! cells

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default copy 4 columns into one, missing empty or #VALUE! cells

    Hi folks,

    i have 4 columns of data that vary in length to copy to one column and i cant figure out a loop to get the 2,3 or 4 columns copied to the bottom of the first

    i have this so far

    Dim x As Integer
    Dim y As Integer
    x = 1
    y = 2
    Do Until Worksheets("Sheet4").Range("A" & x) = ""
    Worksheets("XML").Range("A" & y) = Worksheets("Sheet4").Range("A" & x)
    y = y + 1
    x = x + 1
    Loop

    any guidance much appreciated.

    Carl

  2. #2
    New Member
    Join Date
    Apr 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy 4 columns into one, missing empty or #VALUE! cells

    actually i should add to that, the first column to be copied will always have data. the other columns will have either data or #VALUE error, is there a way of not copying the #VALUE error cells as well?

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    15,316
    Post Thanks / Like
    Mentioned
    272 Post(s)
    Tagged
    22 Thread(s)

    Default Re: copy 4 columns into one, missing empty or #VALUE! cells

    Do the other columns contain formulae?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  4. #4
    New Member
    Join Date
    Apr 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy 4 columns into one, missing empty or #VALUE! cells

    Quote Originally Posted by Fluff View Post
    Do the other columns contain formulae?

    yes, all cells contain an if statement beginning =IF(equals_data!E2>0," although the source cells have an ' in it when no number which i think is causing the #VALUE issue.
    Last edited by dappy; May 21st, 2018 at 09:56 AM.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    15,316
    Post Thanks / Like
    Mentioned
    272 Post(s)
    Tagged
    22 Thread(s)

    Default Re: copy 4 columns into one, missing empty or #VALUE! cells

    When posting formulae using > or < symbols please put a space either side of them, alternatively click Go Advanced & select HTML off.

    Do you just want to copy the values?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  6. #6
    New Member
    Join Date
    Apr 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy 4 columns into one, missing empty or #VALUE! cells

    Quote Originally Posted by Fluff View Post
    When posting formulae using > or < symbols please put a space either side of them, alternatively click Go Advanced & select HTML off.

    Understood thanks

    Quote Originally Posted by Fluff View Post
    Do you just want to copy the values?

    yes just the values

    thanks for looking at this.

    Carl

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    15,316
    Post Thanks / Like
    Mentioned
    272 Post(s)
    Tagged
    22 Thread(s)

    Default Re: copy 4 columns into one, missing empty or #VALUE! cells

    How about
    Code:
    Sub CopyCols()
       Dim Ary As Variant, Nary As Variant
       Dim r As Long, c As Long, j As Long
       Ary = Intersect(Sheets("sheet4").UsedRange, Sheets("sheet4").Range("A:D"))
       ReDim Nary(1 To UBound(Ary, 1) * 4)
       For c = 1 To UBound(Ary, 2)
          For r = 1 To UBound(Ary, 1)
             If Not IsEmpty(Ary(r, c)) And Not IsError(Ary(r, c)) Then
                j = j + 1
                Nary(j) = Ary(r, c)
             End If
          Next r
       Next c
       Sheets("XML").Range("A1").Resize(j).Value = Application.Transpose(Nary)
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  8. #8
    New Member
    Join Date
    Apr 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy 4 columns into one, missing empty or #VALUE! cells

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Sub CopyCols()
       Dim Ary As Variant, Nary As Variant
       Dim r As Long, c As Long, j As Long
       Ary = Intersect(Sheets("sheet4").UsedRange, Sheets("sheet4").Range("A:D"))
       ReDim Nary(1 To UBound(Ary, 1) * 4)
       For c = 1 To UBound(Ary, 2)
          For r = 1 To UBound(Ary, 1)
             If Not IsEmpty(Ary(r, c)) And Not IsError(Ary(r, c)) Then
                j = j + 1
                Nary(j) = Ary(r, c)
             End If
          Next r
       Next c
       Sheets("XML").Range("A1").Resize(j).Value = Application.Transpose(Nary)
    End Sub

    wow, i dont understand any of that :D

    but unfortunately i get a run time error 13 type mismatch.

    am i using this incorrectly?

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    15,316
    Post Thanks / Like
    Mentioned
    272 Post(s)
    Tagged
    22 Thread(s)

    Default Re: copy 4 columns into one, missing empty or #VALUE! cells

    What line gives that error?
    Also, roughly, how many rows do you have?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  10. #10
    New Member
    Join Date
    Apr 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy 4 columns into one, missing empty or #VALUE! cells

    Quote Originally Posted by Fluff View Post
    What line gives that error?
    Debug runs through until J=752

    then fails on

    Sheets("XML").Range("A1").Resize(j).Value = Application.Transpose(Nary)



    Quote Originally Posted by Fluff View Post
    Also, roughly, how many rows do you have?
    there will be less than one hundred lines in each column. this doesnt copy any at the moment.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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