Array code only works on selected sheet

  • Thread starter Thread starter Howard
  • Start date Start date
H

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.

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
 
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.
Click to expand...

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.
 
Hi Howard,

Am Sat, 23 Nov 2013 10:26:33 +0100 schrieb Claus Busch:
why do you delete cell by cell?
Click to expand...

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.
 
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.
Click to expand...


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
 
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.
Click to expand...

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.
Click to expand...

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.
 
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.
Click to expand...

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
 
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
 
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.
Click to expand...

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....
Click to expand...

Yes, both statements are correct. First for adjacent and second for not
adjacent columns.


Regards
Claus B.
 
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.
Click to expand...

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 allthe 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 columnmay 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.
 
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.
Click to expand...

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.
 
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.
Click to expand...


Oh yes! It was worth it. Ran the code in .50 seconds.

Can't thank you enough!

Regards,
Howard
 
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.
Click to expand...

nice ;-)

Almost always you are saving time using the exact range especially in
the newer versions with the millions of cells


Regards
Claus B.
 
Hi Howard,



Am Sun, 24 Nov 2013 10:40:33 -0800 (PST) schrieb Howard:






nice ;-)



Almost always you are saving time using the exact range especially in

the newer versions with the millions of cells





Regards

Claus B.
Click to expand...

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
 
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.
Click to expand...

that is because you use LookAt:=xlPart in your code.
Change it to LookAt:=xlWhole


Regards
Claus B.
 
Back
Top