Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting rows by excluding decimal containing rows

    Hi,
    I am working on a data having around 70000 rows.
    The 1st column consists of data points as 1,1.1,1.2,1.3,1.4,1.5,1.6,1.7,1.8,1.9,2,2.1........... so on.

    What I am interested in is the rows with only natural numbers that is 1,2,3,4. I want to remove or sort all the rows containing decimal numbers like 1.1,1.2,etc.

    Is there any way to do this?

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    Welcome to the Lounge as a new poster!

    There are several ways you can do this.
    Suppose your numeric data is in column [A], starting in row 2 underneath a heading in cell [A1].
    You could apply a custom format to the data to show the 'integer' cells with a chosen background cell colour.
    And then you could apply an Advanced Filter to show only those rows.
    See attached sample file. This has two buttons to show hiding/showing the integer value rows.
    This applies to Excel2007 or later, which allows 'filter-by-colour'.
    ..since you said you had more than 65536 rows, I have deduced you are not working with an earlier Excel version!

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2015-03-19 at 06:03.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    One of the other methods Zeddy mentions would be to test the value in Column A. If it is not an integer, then hide or delete the row.

    HTH,
    Maud

    Code:
    Public Sub HideIntegerRows()
    Application.ScreenUpdating = False
    Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For I = 2 To Lastrow
        If Cells(I, 1) <> Int(Cells(I, 1)) Then
            Cells(I, 1).EntireRow.Hidden = True
        End If
    Next I
    Application.ScreenUpdating = True
    End Sub
    
    Public Sub UnhideIntegerRows()
    Application.ScreenUpdating = False
        Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Range(Cells(2, 1), Cells(Lastrow, 1)).EntireRow.Hidden = False
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    KISS
    Sub FilterONLYWholeNumbers()
    Range("c2").Formula = "=INT(A2)=A2" Columns(1).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("C1:C2"), _
    Unique:=False
    Range("c2").ClearContents
    End Sub

    Sub Unfilter()
    ActiveSheet.ShowAllData
    End Sub
    Last edited by Supershoe; 2015-03-19 at 10:56.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Don

    I like your FilterONLYWholeNumbers routine.
    As we all know, ActiveSheet.ShowAllData will give a debug error if there is no filter currently selected.

    I think of your 'Unfilter' as 'show all'. And if you have 'show all', you will also get a debug error (again) if you then try your unfilter routine again.

    This is not the same as 'filter OFF', which doesn't give the debug error if you run it twice or more.
    It's not just about Keep It Simple, it's about not giving debug errors.
    And here's a shorter filter routine based on your code:
    Code:
    Sub FilterONLYWholeNumbers()
    [c2] = "=INT(A2)=A2"
    Columns(1).AdvancedFilter 1, [c1:c2]
    [c2] = ""
    End Sub

    zeddy

  6. #6
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Reasons not to use shortcut method and what if another advanced filter used filtering elsewhere and unique.

    Memory Considerations:
    While it may be thought that shortcut notation (being much shorter) should make an Excel file a little smaller in size, the reverse is actually true.

    During run-time Excel always uses conventional notation (or so I've been told), so when the code is being compiled all references in shortcut notation must be converted to conventional range form (or so I've been told). {ie [A150] must be converted to Range("A150") form}. Whatever the truth of what I've been told, Visual Basic has to memorize both its compiled version of the code and whatever notation you used to write your code (i.e. whatever's in the code module), the workbook properties for the file size (the memory used) thus goes up slightly.

    However, once it's compiled and saved it should then take no longer to then run, as your written code (i.e. the code that's visible in the code-pane) is completely ignored during run-time ? only the compiled code is used. (Note: To compile your code manually, go to Debug on the VBE toolbar and click Compile VBA Project, then Save).


    Memory Considerations Example
    As an example of the increase in file size, here are the statistics for a file that was converted from conventional to shortcut notation:

    Using conventional notation:

    Before Compilation: 218,624 Bytes

    After Compilation: 255,488 Bytes

    After converting all code to shortcut notation the file size went down slightly but then increased again after compiling.

    Using shortcut notation:

    Before Compilation: 253,440 Bytes

    After Compilation: 256,512 Bytes

    I.e. When comparing the file size after compilation, the file size increased by ~0.4%
    [BREAK=Authors Addendum]
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Don

    I like your FilterONLYWholeNumbers routine.
    I don't use shortcut notation to save file size.
    There are no cost savings in Memory.
    There are no detectable performance issues.

    As you have shown, the file size effect is trivial, at 0.4% in your example.
    A file saved in .xlsb format, as compared with .xlsm format, can easily be 100 times more efficient and effective than a 0.4% reduction.
    A 50% saving in file size is definitely worthwile.

    Truth is that shortcut notation is
    quicker to write (less typing)
    easier to read
    lean and mean
    simple to use

    But what I really like about this forum is the demonstration of different ways of doing things.
    Everyone has their own ideas. And we all pick up new ways of looking at 'problems'.
    Also, a chance to show some of Excel features and vba methods that others may not know.
    Which is why I chose to use and show the 'filter-by-colour' method.

    zeddy
    Excel Consultant

Posting Permissions

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