Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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. -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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. -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Jim,
In addition to my earlier message, you referenced to "F2" on one of the lines (Set rngToSearch = .Range(.Range("F2"), .Cells(Rows.Count, "F").End). May I ask why? Thanks. "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. -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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. -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Your code looked at columns(6). That's column F as a letter.
Jim's code: Set rngToSearch = .Range(.Range("F2"), .Cells(Rows.Count, "F").End(xlUp)) (with that xlup added!) looks at F2 to the last used cell in column F. Please Help wrote: Jim, In addition to my earlier message, you referenced to "F2" on one of the lines (Set rngToSearch = .Range(.Range("F2"), .Cells(Rows.Count, "F").End). May I ask why? Thanks. "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. -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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. -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Jim,
Thanks again very much for your help. I just want to tell you that your code works perfectly. Could you share with me how your code is different from mine (just wanting to gain some knowledge)? 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. -- Dave Peterson |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
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. -- |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Jim's code looks at F2 as the first cell--not the last.
I imagine that he figured that you had header information in row 1. If you don't have headers in row 1, you can change F2 to F1. Please Help wrote: 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. -- -- Dave Peterson |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Set rngToSearch = .Range(.Range("F2"), .Cells(Rows.Count, "F").End(xlUp))
Starts in F2 and looks at the bottom of column F (then upward) to find the last used cell in column F. But the code For Each s2Record In rngToSearch will look from F2 to F#### Dave Peterson wrote: Jim's code looks at F2 as the first cell--not the last. I imagine that he figured that you had header information in row 1. If you don't have headers in row 1, you can change F2 to F1. Please Help wrote: 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. -- -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Slow code when used as VBA code instead of macro (copying visible columns) | Excel Programming | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |