+ Reply to Thread
Results 1 to 7 of 7

Sorting problem

  1. #1
    Bri
    Guest

    Sorting problem

    This one has me stymied. My worksheet collects data in columns A and B
    corresponding to ID and MonSales. This is repeated in columns C and D
    corresponding to ID and TueSales, and so on. Repeats of values in any
    column are allowed.

    My problem is that there are many instances where a row has no value of A
    or B. I have code that sorts A:B by A ascending, then B descending, but
    these 'blanks' go to the top of the sort. I don't know how to get rid of
    them. Deleting rows doesn't work because it interferes with the data in
    C:D, E:F, etc. Needless to say, this sorting needs to be done for each
    column pair.

    I would very much appreciate help with this.
    Thanks - Bri





  2. #2
    Tom Ogilvy
    Guest

    Re: Sorting problem

    Dim rng as Range
    Dim rng1 as Range
    On Error Resume Next
    set rng = Columns(2).SpecialCells(xlblanks)
    set rng1 = Intersect(rng.EntireRow,Columns("A:B"))
    rng1.Delete shift:=xlShiftUp
    On Error goto 0

    --
    Regards,
    Tom Ogilvy


    "Bri" <bdorr@idirect.ca> wrote in message
    news:%23ZkuD1SUGHA.5908@TK2MSFTNGP14.phx.gbl...
    > This one has me stymied. My worksheet collects data in columns A and B
    > corresponding to ID and MonSales. This is repeated in columns C and D
    > corresponding to ID and TueSales, and so on. Repeats of values in any
    > column are allowed.
    >
    > My problem is that there are many instances where a row has no value of A
    > or B. I have code that sorts A:B by A ascending, then B descending, but
    > these 'blanks' go to the top of the sort. I don't know how to get rid of
    > them. Deleting rows doesn't work because it interferes with the data in
    > C:D, E:F, etc. Needless to say, this sorting needs to be done for each
    > column pair.
    >
    > I would very much appreciate help with this.
    > Thanks - Bri
    >
    >
    >
    >




  3. #3
    Bri
    Guest

    Re: Sorting problem

    Tom - thanks for your post

    The code you wrote works perfectly on 'mock' data that I used to test it.
    But... in my actual worksheet, it doesn't get rid of the blank rows. I
    think its because all of the cells including the blank ones resulted from a
    paste action using:

    rng.PasteSpecial Paste:=xlPasteValues

    I don't see anything in these 'blank' cells, but when I select then delete
    them, your code works perfectly.

    any ideas?
    TQS, Bri



    "Tom Ogilvy" <twogilvy@msn.com> wrote in message
    news:%23FHc9LTUGHA.2156@tk2msftngp13.phx.gbl...
    > Dim rng as Range
    > Dim rng1 as Range
    > On Error Resume Next
    > set rng = Columns(2).SpecialCells(xlblanks)
    > set rng1 = Intersect(rng.EntireRow,Columns("A:B"))
    > rng1.Delete shift:=xlShiftUp
    > On Error goto 0
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "Bri" <bdorr@idirect.ca> wrote in message
    > news:%23ZkuD1SUGHA.5908@TK2MSFTNGP14.phx.gbl...
    >> This one has me stymied. My worksheet collects data in columns A and B
    >> corresponding to ID and MonSales. This is repeated in columns C and D
    >> corresponding to ID and TueSales, and so on. Repeats of values in any
    >> column are allowed.
    >>
    >> My problem is that there are many instances where a row has no value of
    >> A
    >> or B. I have code that sorts A:B by A ascending, then B descending, but
    >> these 'blanks' go to the top of the sort. I don't know how to get rid of
    >> them. Deleting rows doesn't work because it interferes with the data in
    >> C:D, E:F, etc. Needless to say, this sorting needs to be done for each
    >> column pair.
    >>
    >> I would very much appreciate help with this.
    >> Thanks - Bri
    >>
    >>
    >>
    >>

    >
    >




  4. #4
    Bri
    Guest

    Re: Sorting problem

    Tom - thanks for your post

    The code you wrote works perfectly on 'mock' data that I used to test it.
    But... in my actual worksheet, it doesn't get rid of the blank rows. I
    think its because all of the cells including the blank ones resulted from a
    paste action using:

    rng.PasteSpecial Paste:=xlPasteValues

    I don't see anything in these 'blank' cells (their pasted value is "" from
    the formuls
    '=IF(code here,"",more code)' ) but when I select then delete
    these cells, your code works perfectly. Its almost as if "" isn't the same
    as blank.

    any ideas?
    TQS, Bri

    "Tom Ogilvy" <twogilvy@msn.com> wrote in message
    news:%23FHc9LTUGHA.2156@tk2msftngp13.phx.gbl...
    > Dim rng as Range
    > Dim rng1 as Range
    > On Error Resume Next
    > set rng = Columns(2).SpecialCells(xlblanks)
    > set rng1 = Intersect(rng.EntireRow,Columns("A:B"))
    > rng1.Delete shift:=xlShiftUp
    > On Error goto 0
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "Bri" <bdorr@idirect.ca> wrote in message
    > news:%23ZkuD1SUGHA.5908@TK2MSFTNGP14.phx.gbl...
    >> This one has me stymied. My worksheet collects data in columns A and B
    >> corresponding to ID and MonSales. This is repeated in columns C and D
    >> corresponding to ID and TueSales, and so on. Repeats of values in any
    >> column are allowed.
    >>
    >> My problem is that there are many instances where a row has no value of
    >> A
    >> or B. I have code that sorts A:B by A ascending, then B descending, but
    >> these 'blanks' go to the top of the sort. I don't know how to get rid of
    >> them. Deleting rows doesn't work because it interferes with the data in
    >> C:D, E:F, etc. Needless to say, this sorting needs to be done for each
    >> column pair.
    >>
    >> I would very much appreciate help with this.
    >> Thanks - Bri
    >>
    >>
    >>
    >>

    >
    >




  5. #5
    Tom Ogilvy
    Guest

    Re: Sorting problem

    You are correct, "" isn't the same a blank. If column B contains numbers
    except for the apparent blank cells, the you can differentiate number and
    text

    Dim rng as Range
    Dim rng1 as Range
    On Error Resume Next
    set rng = Columns(2).SpecialCells(xlConstants,xlTextValues)
    set rng1 = Intersect(rng.EntireRow,Columns("A:B"))
    rng1.Delete shift:=xlShiftUp
    On Error goto 0

    obviously test this on a copy of your data to see if it is deleting the
    correct rows.

    --
    Regards,
    Tom Ogilvy




    "Bri" wrote:

    > Tom - thanks for your post
    >
    > The code you wrote works perfectly on 'mock' data that I used to test it.
    > But... in my actual worksheet, it doesn't get rid of the blank rows. I
    > think its because all of the cells including the blank ones resulted from a
    > paste action using:
    >
    > rng.PasteSpecial Paste:=xlPasteValues
    >
    > I don't see anything in these 'blank' cells (their pasted value is "" from
    > the formuls
    > '=IF(code here,"",more code)' ) but when I select then delete
    > these cells, your code works perfectly. Its almost as if "" isn't the same
    > as blank.
    >
    > any ideas?
    > TQS, Bri
    >
    > "Tom Ogilvy" <twogilvy@msn.com> wrote in message
    > news:%23FHc9LTUGHA.2156@tk2msftngp13.phx.gbl...
    > > Dim rng as Range
    > > Dim rng1 as Range
    > > On Error Resume Next
    > > set rng = Columns(2).SpecialCells(xlblanks)
    > > set rng1 = Intersect(rng.EntireRow,Columns("A:B"))
    > > rng1.Delete shift:=xlShiftUp
    > > On Error goto 0
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > > "Bri" <bdorr@idirect.ca> wrote in message
    > > news:%23ZkuD1SUGHA.5908@TK2MSFTNGP14.phx.gbl...
    > >> This one has me stymied. My worksheet collects data in columns A and B
    > >> corresponding to ID and MonSales. This is repeated in columns C and D
    > >> corresponding to ID and TueSales, and so on. Repeats of values in any
    > >> column are allowed.
    > >>
    > >> My problem is that there are many instances where a row has no value of
    > >> A
    > >> or B. I have code that sorts A:B by A ascending, then B descending, but
    > >> these 'blanks' go to the top of the sort. I don't know how to get rid of
    > >> them. Deleting rows doesn't work because it interferes with the data in
    > >> C:D, E:F, etc. Needless to say, this sorting needs to be done for each
    > >> column pair.
    > >>
    > >> I would very much appreciate help with this.
    > >> Thanks - Bri
    > >>
    > >>
    > >>
    > >>

    > >
    > >

    >
    >
    >


  6. #6
    Bri
    Guest

    Re: Sorting problem

    Thanks Tom - this may help

    As an alternate, is there a way to write NOTHING to a cell instead of "", if
    a condition is not met.

    Something like... =IF(condition here, NULL, value that could be text,
    number, etc)

    thks, Bri


    "Tom Ogilvy" <TomOgilvy@discussions.microsoft.com> wrote in message
    news:599AB0CC-144A-4D64-9960-1479C4240A00@microsoft.com...
    > You are correct, "" isn't the same a blank. If column B contains
    > numbers
    > except for the apparent blank cells, the you can differentiate number and
    > text
    >
    > Dim rng as Range
    > Dim rng1 as Range
    > On Error Resume Next
    > set rng = Columns(2).SpecialCells(xlConstants,xlTextValues)
    > set rng1 = Intersect(rng.EntireRow,Columns("A:B"))
    > rng1.Delete shift:=xlShiftUp
    > On Error goto 0
    >
    > obviously test this on a copy of your data to see if it is deleting the
    > correct rows.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    >
    >
    > "Bri" wrote:
    >
    >> Tom - thanks for your post
    >>
    >> The code you wrote works perfectly on 'mock' data that I used to test it.
    >> But... in my actual worksheet, it doesn't get rid of the blank rows. I
    >> think its because all of the cells including the blank ones resulted
    >> from a
    >> paste action using:
    >>
    >> rng.PasteSpecial Paste:=xlPasteValues
    >>
    >> I don't see anything in these 'blank' cells (their pasted value is ""
    >> from
    >> the formuls
    >> '=IF(code here,"",more code)' ) but when I select then delete
    >> these cells, your code works perfectly. Its almost as if "" isn't the
    >> same
    >> as blank.
    >>
    >> any ideas?
    >> TQS, Bri
    >>
    >> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
    >> news:%23FHc9LTUGHA.2156@tk2msftngp13.phx.gbl...
    >> > Dim rng as Range
    >> > Dim rng1 as Range
    >> > On Error Resume Next
    >> > set rng = Columns(2).SpecialCells(xlblanks)
    >> > set rng1 = Intersect(rng.EntireRow,Columns("A:B"))
    >> > rng1.Delete shift:=xlShiftUp
    >> > On Error goto 0
    >> >
    >> > --
    >> > Regards,
    >> > Tom Ogilvy
    >> >
    >> >
    >> > "Bri" <bdorr@idirect.ca> wrote in message
    >> > news:%23ZkuD1SUGHA.5908@TK2MSFTNGP14.phx.gbl...
    >> >> This one has me stymied. My worksheet collects data in columns A and
    >> >> B
    >> >> corresponding to ID and MonSales. This is repeated in columns C and D
    >> >> corresponding to ID and TueSales, and so on. Repeats of values in any
    >> >> column are allowed.
    >> >>
    >> >> My problem is that there are many instances where a row has no value
    >> >> of
    >> >> A
    >> >> or B. I have code that sorts A:B by A ascending, then B descending,
    >> >> but
    >> >> these 'blanks' go to the top of the sort. I don't know how to get rid
    >> >> of
    >> >> them. Deleting rows doesn't work because it interferes with the data
    >> >> in
    >> >> C:D, E:F, etc. Needless to say, this sorting needs to be done for
    >> >> each
    >> >> column pair.
    >> >>
    >> >> I would very much appreciate help with this.
    >> >> Thanks - Bri
    >> >>
    >> >>
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>
    >>




  7. #7
    Tom Ogilvy
    Guest

    Re: Sorting problem

    No.

    --
    Regards,
    Tom Ogilvy

    "Bri" <bdorr@idirect.ca> wrote in message
    news:eo5iAUbUGHA.4340@TK2MSFTNGP10.phx.gbl...
    > Thanks Tom - this may help
    >
    > As an alternate, is there a way to write NOTHING to a cell instead of "",

    if
    > a condition is not met.
    >
    > Something like... =IF(condition here, NULL, value that could be text,
    > number, etc)
    >
    > thks, Bri
    >
    >
    > "Tom Ogilvy" <TomOgilvy@discussions.microsoft.com> wrote in message
    > news:599AB0CC-144A-4D64-9960-1479C4240A00@microsoft.com...
    > > You are correct, "" isn't the same a blank. If column B contains
    > > numbers
    > > except for the apparent blank cells, the you can differentiate number

    and
    > > text
    > >
    > > Dim rng as Range
    > > Dim rng1 as Range
    > > On Error Resume Next
    > > set rng = Columns(2).SpecialCells(xlConstants,xlTextValues)
    > > set rng1 = Intersect(rng.EntireRow,Columns("A:B"))
    > > rng1.Delete shift:=xlShiftUp
    > > On Error goto 0
    > >
    > > obviously test this on a copy of your data to see if it is deleting the
    > > correct rows.
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > >
    > >
    > > "Bri" wrote:
    > >
    > >> Tom - thanks for your post
    > >>
    > >> The code you wrote works perfectly on 'mock' data that I used to test

    it.
    > >> But... in my actual worksheet, it doesn't get rid of the blank rows.

    I
    > >> think its because all of the cells including the blank ones resulted
    > >> from a
    > >> paste action using:
    > >>
    > >> rng.PasteSpecial Paste:=xlPasteValues
    > >>
    > >> I don't see anything in these 'blank' cells (their pasted value is ""
    > >> from
    > >> the formuls
    > >> '=IF(code here,"",more code)' ) but when I select then delete
    > >> these cells, your code works perfectly. Its almost as if "" isn't the
    > >> same
    > >> as blank.
    > >>
    > >> any ideas?
    > >> TQS, Bri
    > >>
    > >> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
    > >> news:%23FHc9LTUGHA.2156@tk2msftngp13.phx.gbl...
    > >> > Dim rng as Range
    > >> > Dim rng1 as Range
    > >> > On Error Resume Next
    > >> > set rng = Columns(2).SpecialCells(xlblanks)
    > >> > set rng1 = Intersect(rng.EntireRow,Columns("A:B"))
    > >> > rng1.Delete shift:=xlShiftUp
    > >> > On Error goto 0
    > >> >
    > >> > --
    > >> > Regards,
    > >> > Tom Ogilvy
    > >> >
    > >> >
    > >> > "Bri" <bdorr@idirect.ca> wrote in message
    > >> > news:%23ZkuD1SUGHA.5908@TK2MSFTNGP14.phx.gbl...
    > >> >> This one has me stymied. My worksheet collects data in columns A

    and
    > >> >> B
    > >> >> corresponding to ID and MonSales. This is repeated in columns C and

    D
    > >> >> corresponding to ID and TueSales, and so on. Repeats of values in

    any
    > >> >> column are allowed.
    > >> >>
    > >> >> My problem is that there are many instances where a row has no

    value
    > >> >> of
    > >> >> A
    > >> >> or B. I have code that sorts A:B by A ascending, then B descending,
    > >> >> but
    > >> >> these 'blanks' go to the top of the sort. I don't know how to get

    rid
    > >> >> of
    > >> >> them. Deleting rows doesn't work because it interferes with the

    data
    > >> >> in
    > >> >> C:D, E:F, etc. Needless to say, this sorting needs to be done for
    > >> >> each
    > >> >> column pair.
    > >> >>
    > >> >> I would very much appreciate help with this.
    > >> >> Thanks - Bri
    > >> >>
    > >> >>
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1