Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Extract and arrange the numbers small to larger values

  1. #11
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,782
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    Quote Originally Posted by DanteAmor View Post
    Try this, This should write from P to Y without altering other columns.

    Code:
    Sub arrange_numbers1()
      Dim c As Range, j As Long
      For Each c In Range("E4:N" & Range("E" & Rows.Count).End(xlUp).Row)
        If c.Column = 5 Then j = Columns("P").Column
        If c <> 0 Then
          Cells(c.Row, j) = c
          j = j + 1
        End If
      Next
    End Sub
    DanteAmor, yes this is spot on running perfect, thank you for your help

    Kind Regards,
    Moti

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,226
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    Another option
    Code:
    Sub Motilulla()
        Dim Ary As Variant, Nary As Variant
        Dim r As Long, c As Long, nc As Long
        
        Ary = Range("E4", Range("N" & Rows.Count).End(xlUp)).Value2
        ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
        For r = 1 To UBound(Ary)
            For c = 1 To UBound(Ary, 2)
                If Ary(r, c) > 0 Then
                    nc = nc + 1
                    Nary(r, nc) = Ary(r, c)
                End If
            Next c
            nc = 0
        Next r
        Range("P4").Resize(UBound(Nary), 10).Value = Nary
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,975
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    Quote Originally Posted by motilulla View Post
    DanteAmor, yes this is spot on running perfect, thank you for your help

    Kind Regards,
    Moti

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

  4. #14
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,782
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    Quote Originally Posted by mohadin View Post
    HI
    What about
    Code:
    Sub test()
        Dim b As Variant
        Dim lr, i
           For i = 5 To Cells(Rows.Count, 5).End(xlUp).Row
            ReDim b(1 To 10)
            t = 1
            For j = 5 To 15
                If Cells(i, j) <> 0 Then
                    b(t) = Cells(i, j): t = t + 1
                End If
            Next
            Cells(i, 16).Resize(, UBound(b)) = b
        Next
    End Sub
    mohadin, yes your code also worked fine,just changed 5 to 4, thank you for your help
    Code:
    For i = 4 To Cells(Rows.Count, 5).End(xlUp).Row

    Kind Regards,
    Moti

  5. #15
    Board Regular
    Join Date
    Feb 2008
    Posts
    1,782
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    Quote Originally Posted by Fluff View Post
    Another option
    Code:
    Sub Motilulla()
        Dim Ary As Variant, Nary As Variant
        Dim r As Long, c As Long, nc As Long
        
        Ary = Range("E4", Range("N" & Rows.Count).End(xlUp)).Value2
        ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
        For r = 1 To UBound(Ary)
            For c = 1 To UBound(Ary, 2)
                If Ary(r, c) > 0 Then
                    nc = nc + 1
                    Nary(r, nc) = Ary(r, c)
                End If
            Next c
            nc = 0
        Next r
        Range("P4").Resize(UBound(Nary), 10).Value = Nary
    End Sub
    Fluff, Thank you for the help, yes your code also gave a required result Ok.

    Kind Regards,
    Moti

  6. #16
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,226
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    My pleasure & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #17
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    331
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract and arrange the numbers small to larger values

    Hi Moti
    Thank you for the feed back
    Be Happy

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
  •