Two column deletion
 

How can i put these 2 sub into 1 ?
Currently to speed up deletion i sort accordingly like

Sort by column(12)
Run DelPost
Sort by column(11)
Run DelPre.
---------------------------------

Sub DelPost()
On Error Resume Next
With ActiveSheet.Columns(12)
.ColumnWidth = 20
.Replace _
What:="Post", _
Replacement:="#N/A", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
End Sub

Sub DelPre()
On Error Resume Next
With ActiveSheet.Columns(11)
.Replace _
What:="Pre", _
Replacement:="#N/A", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
End Sub

Thank you in advance.

Two column deletion
 

Try this...

Sub Delete_PreAndPost()
On Error Resume Next
With ActiveSheet.Range(ActiveSheet.Columns(11).Address) .Resize(, 2)
.Replace What:="Pre", Replacement:="#N/A", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="Post", Replacement:="#N/A", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Columns(1).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
.Columns(2).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Two column deletion
 

.Columns(1).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
.Columns(2).SpecialCells(xlConstants, xlErrors).EntireRow.Delete

The above two lines of code from your posted code can be converted into a
single line...

..Range("A:B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete

Rick Rothstein (MVP - Excel)

Two column deletion
 

Rick Rothstein has brought this to us :
.Columns(1).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
.Columns(2).SpecialCells(xlConstants, xlErrors).EntireRow.Delete

The above two lines of code from your posted code can be converted into a
single line...

.Range("A:B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete

Rick Rothstein (MVP - Excel)

True IF there's only 1 error per row. Otherwise, nothing gets deleted!
I suppose we can assume there's only 1 criteria value per row but that
doesn't cover it if a user screws up OR values get put in both columns.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Two column deletion
 

True IF there's only 1 error per row. Otherwise, nothing gets deleted!

Good point! I had forgotten about that problem.

Rick Rothstein (MVP - Excel)

Two column deletion
 

Rick Rothstein was thinking very hard :
True IF there's only 1 error per row. Otherwise, nothing gets deleted!

Good point! I had forgotten about that problem.

Rick Rothstein (MVP - Excel)

Well, I never knew about it until I tried your suggestion first (as it
was already coded in OP's sample). That was an eye opener when the
target rows didn't delete. Seems it only takes 1 instance of two
entries for SpecialCells to fail!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Two column deletion
 

Garry, Rick thank you so much indeed.
I have now one sub instead of two but I lost the speed mysteriously.
I mean sorting before deletion method was faster somehow.
Could the number of rows be the reason which is more than 5K ?
Win XP, Office XP

Two column deletion
 

Sorry, please ignore the last message.
I placed the sorting codes and now i have the lightining speed again.
So it has to be 2 separate deletions otherwise no proper sorting.

Thank you very much once again.

Two column deletion
 

Rick Rothstein explained :
True IF there's only 1 error per row. Otherwise, nothing gets deleted!

Good point! I had forgotten about that problem.

Rick Rothstein (MVP - Excel)

Just to add 2c worth...
This data appears to be such that could be stored in a single column.
Best practices for good spreadsheet design suggest that storing these
values in a single col (field) removes any ambiguity that may arise
from both fields being populated. (User could select from cell dropdown
OR leave blank) Unless there's some functional reason why this data
needs to be stored in 2 cols, I don't see any benefit.<g

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Two column deletion
 

.Range("A:B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete

True IF there's only 1 error per row. Otherwise, nothing gets deleted!

Hi Garry
I don't understand I have lots rows that had 2 "#N/A" next to each
other and Rick's code works.
Maybe you are talking about something else.
Regards

Two column deletion
 

darkblue was thinking very hard :
.Range("A:B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete

True IF there's only 1 error per row. Otherwise, nothing gets deleted!

Hi Garry
I don't understand I have lots rows that had 2 "#N/A" next to each
other and Rick's code works.
Maybe you are talking about something else.
Regards

The delete doesn't work when there's 2 "#N/A" next to each other on the
same row. That causes SpecialCells to fail because it's forcing 2
deletes for that row which, after the first delete, the row no longer
exists.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Two column deletion
 

darkblue presented the following explanation :
.Range("A:B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete

True IF there's only 1 error per row. Otherwise, nothing gets deleted!

Hi Garry
I don't understand I have lots rows that had 2 "#N/A" next to each
other and Rick's code works.
Maybe you are talking about something else.
Regards

Maybe you're confused about who posted the code. Rick confirmed his 1
line suggestion for the code I posted won't work when there's 2 errors
on the same row of the target range.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Two column deletion
 

Maybe it is my poor english Garry, sorry.
What i mean is below code works even if
row2, for instance, has two "#N/A" on col1 and col2.
Although i use your original code because of the sorting before
deletion.
Regards

Sub Delete_PreAndPost()
On Error Resume Next
With ActiveSheet.Range(ActiveSheet.Columns(1).Address). Resize(, 2)
.Replace What:="Pre", Replacement:="#N/A", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="Post", Replacement:="#N/A", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Range("A:B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
End Sub

Two column deletion
 

darkblue used his keyboard to write :
Maybe it is my poor english Garry, sorry.
What i mean is below code works even if
row2, for instance, has two "#N/A" on col1 and col2.
Although i use your original code because of the sorting before
deletion.
Regards

Sub Delete_PreAndPost()
On Error Resume Next
With ActiveSheet.Range(ActiveSheet.Columns(1).Address). Resize(, 2)
.Replace What:="Pre", Replacement:="#N/A", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="Post", Replacement:="#N/A", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Range("A:B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
End Sub

Well, you're right! I'm afraid I misunderstood something here but now I
see what Rick was doing. (I should have known better than to Q Rick's
intent)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Two column deletion
 

Rick Rothstein expressed precisely :
.Columns(1).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
.Columns(2).SpecialCells(xlConstants, xlErrors).EntireRow.Delete

The above two lines of code from your posted code can be converted into a
single line...

.Range("A:B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete

Rick Rothstein (MVP - Excel)

Rick, I originally misunderstood what you were suggesting. You're quite
right (as usual) about this suggestion...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Two column deletion
 

Many thanks once again to you all.
Best regards

Two column deletion
 

It happens that darkblue formulated :
Many thanks once again to you all.
Best regards

You're welcome! Thanks to you for persisting about the final solution!
-Much appreciated...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc