Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting problem
Use a formula for those cells or add a new column that check for blanks and then put a suitable value in you can later sort on and then delete.
Something like: =If(B2="",na(),row()) This will sort to the bottom with Ascending, or delete in one go with by using specialcells. Select all the cells that mater include these formulas then press F5 Special, pick Formulas and uncheck all except Errors OK. With these selected cells press Ctrl + (minus sign) and pick whether you want the whole row deleted, or to shift the cells up. Regards Robert McCurdy "Bri" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting problem
No.
-- Regards, Tom Ogilvy "Bri" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting problem | Excel Worksheet Functions | |||
Sorting Problem | Excel Discussion (Misc queries) | |||
Sorting problem | Excel Discussion (Misc queries) | |||
Sorting problem | Excel Programming | |||
Sorting problem | Excel Discussion (Misc queries) |