Hi Dave,

Thanks for taking the time to explain to me.

Can I ask you one more thing? You mentioned that Jim's code is looking up
F2 as the last used cell. How about F1? Why it is not looked up? Is that
code looked up from bottom to top?

Thanks. I wish you have a safe and nice holiday season!

"Dave Peterson" wrote:

Your code:

If Len(Sheets("Sheet 1").Range("E26")) < 6 Then
For Each s2Record in Worksheets("Sheet 2").Columns(6)
If s2Record = "A-1" or s2Record = "B-1" or s2Record = "C-1" Then
s2Record.EntireRow.Delete Shift:=xlShiftUp
end if
Next s2Record
End If

Jim's code:

with Worksheets("Sheet 2")
Set rngToSearch = .Range(.Range("F2"), .Cells(Rows.Count, "F").End(xlUp))
End With
For Each s2Record In rngToSearch
With s2Record
If .Value = "A-1" Or .Value = "B-1" Or .Value = "C-1" Then
If rngToDelete Is Nothing Then
Set rngToDelete = s2Record
Else
Set rngToDelete = Union(rngToDelete, s2Record)
End If
End If
End With
Next s2Record
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

Jim limits the number of cells to F2:F(thelastusedcellincolumnF).
You look at every cell in column F except F1. In xl2003, that's 65535 cells.
In xl2007, that's a million cells. Depending on how many cells are used, that's
a lot of extra cells to inspect.

Your code deletes the rows one at a time.
Jim builds a range variable (using Union) and deletes them all at once.

Depending on how many rows are deleted, you could see a significant increase in
speed by deleting all the rows at the end. Your code could be recalculating
each time a row is deleted. That could be a lot of unnecessary recalcs.


Please Help wrote:

Hi Dave,

Thanks again for your help and patience.

Basically, the Cell E26 of Sheet 1 will have either a value of 6 digits or
blank (no value). If there is no value, I want to execute the succeeding
code (delete all the records containing A-1, B-1 and C-1 in Column F of Sheet
2). On the other hand, if a value is equal to 6 digits, I don't want the
succeeding code to execute. In addition, I thought of a risk that the user
keys in mistakenly by not having 6 digits. That's why, I used the code "If
len(Sheets("Sheet 1").Range("E26")) < 6 Then". However, I didn't think of a
logic how to handle a situation when there is no value.

So I changed my code to:

If Len(Sheets("Sheet 1").Range("E26")) < 6 or _
Len(Sheets("Sheet 1").Range("E26")) = 0 Then

After I changed the code, the code works the way I wanted.

To gain some knowledge from this experience, could you share with me how my
code below is different from Jim's code and what I missed (besides from
experience)?

If Len(Sheets("Sheet 1").Range("E26")) < 6 Then
For Each s2Record in Worksheets("Sheet 2").Columns(6)
If s2Record = "A-1" or s2Record = "B-1" or s2Record = "C-1" Then
s2Record.EntireRow.Delete Shift:=xlShiftUp
end if
Next s2Record
End If

Thanks again very much.

"Dave Peterson" wrote:

I'm not sure I understand what you want.

If E26 is empty, then the length will be 0.

If you want to run the code when E26 has something in it, but only when the
length is different from 6...

if len(worksheets("sheet 1").range("e26").value) = 0 _
or len(worksheets("sheet 1").range("e26").value) = 6 then
'skip it
else
'do all that other code.
end if

I think????



Please Help wrote:

Hi Dave,

I do want the code to execute (deleting all the records with A-1, B-1 and
C-1 in Sheet 2) when the length of E26 is not equal to 6. However, I think
you made me realized one thing.

What would happen if E26 is blank? Would that make the code to avoid from
executing? Do you think I should change it to:

If Len(Sheets("Sheet 1").Range("E26")) < 6 or not isnull(Sheets("Sheet
1").Range("E26") Then

Thanks again for your help and patience.

"Dave Peterson" wrote:

The code:
If Len(Sheets("Sheet 1").Range("E26")) < 6 Then
only runs if the length is different than 6.

It sounds like you want:
If Len(Sheets("Sheet 1").Range("E26")) = 6 Then

or just delete (or comment) that line and its corresponding "End if" line.


Please Help wrote:

The Sheets("Sheet 1").Range("E26") is a text field with a numeric value (e.g.
012345), and it will always have 6 digits.

The values A-1, B-1 and C-1 will always be the way they are (meaning capital
letter with dash and a number), and I don't think they have any leading or
trailing spaces. In fact, I tried "Trim" (e.g. Trim(.value="A-1")) to make
sure they have no leading or trailing spaces, and I received no errors and
those records don't get deleted.

Thanks.

"Dave Peterson" wrote:

What's in Sheets("Sheet 1").Range("E26")? Does it have a length different from
6?

Are your values A-1 or a-1 (upper/lower case difference)?

Do you values have any leading/trailing spaces in them?



Please Help wrote:

Jim,

Thanks for your help and patience. I am no longer receiving errors;
however, the records containing "A-1", "B-1" and "C-1" do not get deleted.
They are still in the "Sheet 2".

Please help. Thanks.

"Jim Thomlinson" wrote:

Give this a try...

Dim rngToDelete As Range
Dim rngToSearch As Range
Dim s2Record As Range

If Len(Sheets("Sheet 1").Range("E26")) < 6 Then
With Worksheets("Sheet 2")
Set rngToSearch = .Range(.Range("F2"), .Cells(Rows.Count, "F").End(xlUp))
End With
For Each s2Record In rngToSearch
With s2Record
If .Value = "A-1" Or .Value = "B-1" Or .Value = "C-1" Then
If rngToDelete Is Nothing Then
Set rngToDelete = s2Record
Else
Set rngToDelete = Union(rngToDelete, s2Record)
End If
End If
End With
Next s2Record
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End If
--
HTH...

Jim Thomlinson


"Please Help" wrote:

Dave,

In addition to my earlier message, the error was pointed me to the same line
that I posted initially (If .value = "A-1" or .value = "B-1" or .value="C-1"
Then).

Thanks.

"Please Help" wrote:

Hi Dave,

Thanks for your response. I added "End If" per your instruction, and I got
another error message "Type Mismatch".

Any idea?

Thanks.

"Dave Peterson" wrote:

Jim missed an "end if" statement:

Dim rngToDelete As Range
Dim s2Record As Range

If Len(Sheets("Sheet 1").Range("E26")) < 6 Then
For Each s2Record In Worksheets("Sheet 2").Columns(6)
With s2Record
If .Value = "A-1" Or .Value = "B-1" Or .Value = "C-1" Then
If rngToDelete Is Nothing Then
Set rngToDelete = s2Record
Else
Set rngToDelete = Union(rngToDelete, s2Record)
End if '<--Added here
End If
End With
Next s2Record
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End If

Please Help wrote:

Hi Jim,

Thanks for the code. When I tried your code, I got an error message "End
With without With", and it pointed me to the line "End With" in your code.

Do you know why?

Thanks.

"Jim Thomlinson" wrote:

For each and deleting do not get along. You will be deleting s2Record which
is an issue, not to mention that the sive of the range you are traversing
will be changing. What you can do (whis is much more efficint eany ways is to
create on big range to be deleted at the end after the for each is done...
something like this...

Dim rngToDelete As Range
Dim s2Record As Range

If Len(Sheets("Sheet 1").Range("E26")) < 6 Then
For Each s2Record In Worksheets("Sheet 2").Columns(6)
With s2Record
If .Value = "A-1" Or .Value = "B-1" Or .Value = "C-1" Then
If rngToDelete Is Nothing Then
Set rngToDelete = s2Record
Else
Set rngToDelete = Union(rngToDelete, s2Record)
End If
End With
Next s2Record
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End If
--
HTH...

Jim Thomlinson


"Please Help" wrote:

Hello all,

I have 2 worksheets, "Sheet 1" and "Sheet 2". First, my code checks the
length of Cell "E26" in "Sheet 1". If the length of E26 is not equal to 6,
delete the records (entire row) in "Sheet 2" with the values "A-1", "B-1" and
"C-1" in column F of Sheet 2. Then move the records up that are below.

The Sheet 2 has over 1000 records and has 10 columns with values. I have
the following code, and when I executed it, I got a Type Mismatch error, and
it pointed me to the line "If s2Record = "A-1" or s2Record = "B-1" or
s2Record = "C-1" Then".

If Len(Sheets("Sheet 1").Range("E26")) < 6 Then
For Each s2Record in Worksheets("Sheet 2").Columns(6)
If s2Record = "A-1" or s2Record = "B-1" or s2Record = "C-1" Then
s2Record.EntireRow.Delete Shift:=xlShiftUp
end if
Next s2Record
End If

Thanks.

--