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.

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.

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.

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.

--

Dave Peterson

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.

--

Dave Peterson

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.

--

Dave Peterson