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

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

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

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

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

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

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

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