Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
The code in a standard module and a list of numbers in sheet 1, column A. I run the code and it only works on whatever sheet is selected. If the sheet is sheet 1 then it eliminates my list in column A. If sheet 2 is selected and I run the code it works for that sheet on the scattered test numbers, and that sheet alone. What is stopping the code from going to sheet 2, then 3 then 4 as I would think it should, given the array elements? Thanks, Howard Option Explicit Sub XNumOut() Dim DeleNum As Range Dim c As Range Dim rngC As Range Dim varSheets As Variant Dim DelC As Range Dim i As Long Dim Lrow As Long Lrow = Range("A" & Rows.Count).End(xlUp).Row Set DeleNum = Sheets("Sheet1").Range("A1:A" & Lrow) varSheets = Array("Sheet2", "Sheet3", "Sheet4") For i = LBound(varSheets) To UBound(varSheets) With Sheets(varSheets(i)) For Each c In DeleNum Cells.Replace What:=c, Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Next End With Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
Hi Howard,
Am Fri, 22 Nov 2013 21:55:09 -0800 (PST) schrieb Howard: The code in a standard module and a list of numbers in sheet 1, column A. I run the code and it only works on whatever sheet is selected. If the sheet is sheet 1 then it eliminates my list in column A. If sheet 2 is selected and I run the code it works for that sheet on the scattered test numbers, and that sheet alone. why do you delete cell by cell? Try: Sub XNumOut() Dim DeleNum As Range Dim c As Range Dim rngC As Range Dim varSheets As Variant Dim i As Long Dim LRow As Long varSheets = Array("Sheet2", "Sheet3", "Sheet4") For i = LBound(varSheets) To UBound(varSheets) With Sheets(varSheets(i)) LRow = .Cells(.Rows.Count, 1).End(xlUp).Row Set DeleNum = .Range("A1:A" & LRow) DeleNum.ClearContents End With Next End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
Hi Howard,
Am Sat, 23 Nov 2013 10:26:33 +0100 schrieb Claus Busch: why do you delete cell by cell? I ran you code and so it deleted all cells I misunderstood your problem. Try: Sub XNumOut() Dim DeleNum As Range Dim c As Range Dim rngC As Range Dim varSheets As Variant Dim DelC As Range Dim i As Long Dim Lrow As Long With Sheets("Sheet1") Lrow = .Cells(.Rows.Count, 1).End(xlUp).Row Set DeleNum = .Range("A1:A" & Lrow) End With varSheets = Array("Sheet2", "Sheet3", "Sheet4") For i = LBound(varSheets) To UBound(varSheets) With Sheets(varSheets(i)) For Each c In DeleNum .UsedRange.Replace What:=c, Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next End With Next End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
why do you delete cell by cell? I ran you code and so it deleted all cells I misunderstood your problem. Try: Sub XNumOut() Dim DeleNum As Range Dim c As Range Dim rngC As Range Dim varSheets As Variant Dim DelC As Range Dim i As Long Dim Lrow As Long With Sheets("Sheet1") Lrow = .Cells(.Rows.Count, 1).End(xlUp).Row Set DeleNum = .Range("A1:A" & Lrow) End With varSheets = Array("Sheet2", "Sheet3", "Sheet4") For i = LBound(varSheets) To UBound(varSheets) With Sheets(varSheets(i)) For Each c In DeleNum .UsedRange.Replace What:=c, Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next End With Next End Sub Regards Claus B. Thanks, Claus. That works fine indeed. After comparing your solution to what I posted, I noticed the .UsedRange (with the .) So I re-tried my code with .Cells and it worked. I guess the reason I used Cells is because I read some pro advice about UsedRange that gave me pause. Did not fully understand the reason to avoid it, (and there was no firm suggestion to ALWAYS avoid it) and may have been in a completely different situation. He cited an experiment, which I cannot recall exactly, and I followed it and indeed it demonstrated how some confusion could occur. I thought about using UsedRange here and decided not for those reasons. Thanks for the help, and I will still keep UsedRange in my mind. I see it working on so many examples and I assume would be faster than .Cells. Regards, Howard |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
Hi Howard,
Am Sat, 23 Nov 2013 02:39:18 -0800 (PST) schrieb Howard: After comparing your solution to what I posted, I noticed the .UsedRange (with the .) So I re-tried my code with .Cells and it worked. without the dot in front you haven't refered the range to look in to your sheets I guess the reason I used Cells is because I read some pro advice about UsedRange that gave me pause. Did not fully understand the reason to avoid it, (and there was no firm suggestion to ALWAYS avoid it) and may have been in a completely different situation. He cited an experiment, which I cannot recall exactly, and I followed it and indeed it demonstrated how some confusion could occur. If you want to avoid UsedRange you could set your correct range with LRow and LCol, e.g.: .Range(Cells(1, 1), Cells(LRow, LCol)) I want to avoid cells because that means all cells of the sheet and these are 17.179.869.184 in the newer excel versions Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
If you want to avoid UsedRange you could set your correct range with LRow and LCol, e.g.: .Range(Cells(1, 1), Cells(LRow, LCol)) I want to avoid cells because that means all cells of the sheet and these are 17.179.869.184 in the newer excel versions Regards Claus B. Gotcha, UsedRange makes the most sense here as it might vary greatly between sheets. And if the UsedRange is a million cells then it's taken care of as well as if it is only a few hundred. Thanks for the further info. Regards, Howard |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
I have a follow up question on the very workable code you offered here.
I have learned that the UsedRange on each of the sheets in the array is quite large, and that in reality a single column in each sheets is where the deletions will take place. The problem is, that it is a different column for each sheet in the array, and that has me stumped. If it was the same column for all sheets I presume you could replace UsedRange with a specific range .Range("F:F").Replace What.... So if the code is looking at the first element in the array, "Sheet2" I need it to look at column O, second element, "Sheet3" to look at column L and the third is column A. And just for info sake if one was to want two columns, adjacent and/or apart what would that look like? ..Range("F:G").Replace What.... ..Range("F:F,K:K").Replace What.... Howard varSheets = Array("Sheet2", "Sheet3", "Sheet4") For i = LBound(varSheets) To UBound(varSheets) With Sheets(varSheets(i)) For Each c In DeleNum .UsedRange.Replace What:=c, Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
Hi Howard,
Am Sun, 24 Nov 2013 02:52:29 -0800 (PST) schrieb Howard: The problem is, that it is a different column for each sheet in the array, and that has me stumped. put your column numbers into another array in the same order as the sheets: Dim DeleNum As Range Dim c As Range Dim rngC As Range Dim varSheets As Variant Dim varCols As Variant Dim DelC As Range Dim i As Long Dim Lrow As Long With Sheets("Sheet1") Lrow = .Cells(.Rows.Count, 1).End(xlUp).Row Set DeleNum = .Range("A1:A" & Lrow) End With varSheets = Array("Sheet2", "Sheet3", "Sheet4") varCols = Array(15, 12, 1) For i = LBound(varSheets) To UBound(varSheets) With Sheets(varSheets(i)) For Each c In DeleNum .Columns(varCols(i)).Replace What:=c, Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next End With Next End Sub And just for info sake if one was to want two columns, adjacent and/or apart what would that look like? .Range("F:G").Replace What.... .Range("F:F,K:K").Replace What.... Yes, both statements are correct. First for adjacent and second for not adjacent columns. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
put your column numbers into another array in the same order as the sheets: Dim DeleNum As Range Dim c As Range Dim rngC As Range Dim varSheets As Variant Dim varCols As Variant Dim DelC As Range Dim i As Long Dim Lrow As Long With Sheets("Sheet1") Lrow = .Cells(.Rows.Count, 1).End(xlUp).Row Set DeleNum = .Range("A1:A" & Lrow) End With varSheets = Array("Sheet2", "Sheet3", "Sheet4") varCols = Array(15, 12, 1) For i = LBound(varSheets) To UBound(varSheets) With Sheets(varSheets(i)) For Each c In DeleNum .Columns(varCols(i)).Replace What:=c, Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next End With Next End Sub Regards Claus B. Thanks Claus. Once written it seems quite straight forward and understandable. Alas, I would have never gotten there on my own. Google failed to turn up an example, where I spent about an 45 min looking. With a silent count it cuts the code down to about 12 to 14 seconds. Previous was maybe 20 seconds. I assume that's a small price to pay when one column is 350 rows and two are about 1200 to 1500 rows. I assume it goes all the way down in each of the column to 1 million + rows. Not sure its worth it to try to row specific in the search range. A column may have 40 number entries then a single "Title Cell" then maybe all blanks until the next "Title Cell" and numbers and blanks and Titles until the end of that columns data. At any rate, thanks for all the help. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
Hi Howard,
Am Sun, 24 Nov 2013 04:16:54 -0800 (PST) schrieb Howard: With a silent count it cuts the code down to about 12 to 14 seconds. Previous was maybe 20 seconds. I assume that's a small price to pay when one column is 350 rows and two are about 1200 to 1500 rows. I assume it goes all the way down in each of the column to 1 million + rows. Not sure its worth it to try to row specific in the search range. A column may have 40 number entries then a single "Title Cell" then maybe all blanks until the next "Title Cell" and numbers and blanks and Titles until the end of that columns data. try it with the correct range: Sub XNumOut() Dim DeleNum As Range Dim c As Range Dim rngC As Range Dim varSheets As Variant Dim varCols As Variant Dim DelC As Range Dim i As Long Dim LRow As Long Dim st As Double st = Timer With Sheets("Sheet1") LRow = .Cells(.Rows.Count, 1).End(xlUp).Row Set DeleNum = .Range("A1:A" & LRow) End With varSheets = Array("Sheet2", "Sheet3", "Sheet4") varCols = Array(15, 12, 1) For i = LBound(varSheets) To UBound(varSheets) With Sheets(varSheets(i)) LRow = .Cells(.Rows.Count, varCols(i)).End(xlUp).Row For Each c In DeleNum .Range(.Cells(1, varCols(i)), .Cells(LRow, varCols(i))) _ .Replace What:=c, Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next End With Next MsgBox Format(Timer - st, "0.000") End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
Not sure its worth it to try to row specific in the search range. A column may have 40 number entries then a single "Title Cell" then maybe all blanks until the next "Title Cell" and numbers and blanks and Titles until the end of that columns data. try it with the correct range: Sub XNumOut() Dim DeleNum As Range Dim c As Range Dim rngC As Range Dim varSheets As Variant Dim varCols As Variant Dim DelC As Range Dim i As Long Dim LRow As Long Dim st As Double st = Timer With Sheets("Sheet1") LRow = .Cells(.Rows.Count, 1).End(xlUp).Row Set DeleNum = .Range("A1:A" & LRow) End With varSheets = Array("Sheet2", "Sheet3", "Sheet4") varCols = Array(15, 12, 1) For i = LBound(varSheets) To UBound(varSheets) With Sheets(varSheets(i)) LRow = .Cells(.Rows.Count, varCols(i)).End(xlUp).Row For Each c In DeleNum .Range(.Cells(1, varCols(i)), .Cells(LRow, varCols(i))) _ .Replace What:=c, Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next End With Next MsgBox Format(Timer - st, "0.000") End Sub Regards Claus B. Oh yes! It was worth it. Ran the code in .50 seconds. Can't thank you enough! Regards, Howard |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
Hi Howard,
Am Sun, 24 Nov 2013 10:40:33 -0800 (PST) schrieb Howard: Oh yes! It was worth it. Ran the code in .50 seconds. nice ;-) Almost always you are saving time using the exact range especially in the newer versions with the millions of cells Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
On Sunday, November 24, 2013 11:20:04 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Sun, 24 Nov 2013 10:40:33 -0800 (PST) schrieb Howard: Oh yes! It was worth it. Ran the code in .50 seconds. nice ;-) Almost always you are saving time using the exact range especially in the newer versions with the millions of cells Regards Claus B. Here is an interesting development I just found with the code as I was testing it further. In the DeleNum = .Range("A1:A" & LRow) column I enter the numbers 0, 1, 2. (Sheet1) In one of the columns of the array I enter 100, 200, 300, 255. After running the code the values in the array column are now 3, 55. So what it is doing is, if there is ANY 0, 1, 2 they all get eliminated even if the 0's were in the number 100, or 200. Also the numbers 111, 222 would be eliminated because the code deletes all the 1's, 2's, etc. Any way to make the code treat the numbers as "whole numbers"? Where if you wanted 111 deleted you would need to have 111 entered in the sheet 1 list. And the same with 1, only eliminates a 1 in the other columns and ignore 111. Howard |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
Hi Howard,
Am Sun, 24 Nov 2013 12:51:31 -0800 (PST) schrieb Howard: In the DeleNum = .Range("A1:A" & LRow) column I enter the numbers 0, 1, 2. (Sheet1) In one of the columns of the array I enter 100, 200, 300, 255. After running the code the values in the array column are now 3, 55. that is because you use LookAt:=xlPart in your code. Change it to LookAt:=xlWhole Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array code only works on selected sheet
that is because you use LookAt:=xlPart in your code. Change it to LookAt:=xlWhole Regards Claus B. Well, that solved that in a blink. Thank you. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code works on sheet but not from code-to-sheet | Excel Programming | |||
Code not work Take same action on all wkshts or selected works | Excel Programming | |||
Array of all selected sheet names? | Excel Programming | |||
Code Only works when sheet is in focus | Excel Programming | |||
Code for current sheet selected... | Excel Programming |