Two column deletion

[darkblue]

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.

 

[GS]

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

 

[Rick Rothstein]

.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)

 

[GS]

Rick Rothstein has brought this to us :

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.

 

[Rick Rothstein]

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)

 

[GS]

Rick Rothstein was thinking very hard :

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!

 

[darkblue]

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

 

[darkblue]

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.

 

[GS]

Rick Rothstein explained :

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>

 

[darkblue]

.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

 

[GS]

darkblue was thinking very hard :

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.

 

[GS]

darkblue presented the following explanation :

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.

 

[darkblue]

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

 

[GS]

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)

 

[GS]

Rick Rothstein expressed precisely :

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...

 

[darkblue]

Many thanks once again to you all.
Best regards

 

[GS]

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...