Hello,
On sheet 1 I use array formulas that have a set row range. Example
(A1:A1000). Sheet 1 requests date from sheet 2.
On sheet 2 there is data imported from another application that leaves page
header info multiple times, and I use a "cleanup" formula to delete the rows
that contain the unwanted page header info.
I have found that the row deletion on sheet 2 changes the ranges in the
formulas on sheet 1. Example, If the cleanup formula deletes 10 rows on sheet
2, the formula range on sheet 1 changes from A1:A1000 to A1:A990. Everytime I
run it, the range is reduced further. This is the cleanup formula:
Sub DeleteRowsRTH()
FindString = "*COMPANY*" 'adjust to company name
Set b = Range("A:H").Find(what:=FindString, lookat:=xlWhole)
While Not (b Is Nothing)
b.Resize(10).EntireRow.Delete
Set b = Range("A:H").Find(what:=FindString, lookat:=xlWhole)
Wend
End Sub

I don't beleive I can use infinite row designations (A:A) for an array
formula. I tried and get a #NUM error. Is there any way I can lock the
ranges on sheet 1 so they don't change?........or perhaps some other approach
to this problem?
thanks,
Robert

Hi,

One way to handle this is to replace the reference A1:A1000 with
INDIRECT("A1:A1000")
or
OFFSET(A1,0,0,1000)

it would be better if we knew the formula you were using.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Robert" wrote:

Hello,
On sheet 1 I use array formulas that have a set row range. Example
(A1:A1000). Sheet 1 requests date from sheet 2.
On sheet 2 there is data imported from another application that leaves page
header info multiple times, and I use a "cleanup" formula to delete the rows
that contain the unwanted page header info.
I have found that the row deletion on sheet 2 changes the ranges in the
formulas on sheet 1. Example, If the cleanup formula deletes 10 rows on sheet
2, the formula range on sheet 1 changes from A1:A1000 to A1:A990. Everytime I
run it, the range is reduced further. This is the cleanup formula:
Sub DeleteRowsRTH()
FindString = "*COMPANY*" 'adjust to company name
Set b = Range("A:H").Find(what:=FindString, lookat:=xlWhole)
While Not (b Is Nothing)
b.Resize(10).EntireRow.Delete
Set b = Range("A:H").Find(what:=FindString, lookat:=xlWhole)
Wend
End Sub

I don't beleive I can use infinite row designations (A:A) for an array
formula. I tried and get a #NUM error. Is there any way I can lock the
ranges on sheet 1 so they don't change?........or perhaps some other approach
to this problem?
thanks,
Robert

Robert wrote...
....
I don't beleive I can use infinite row designations (A:A) for an array
formula. I tried and get a *#NUM error. Is there any way I can lock the
ranges on sheet 1 so they don't change?........or perhaps some other approach
to this problem?

You could either change your macro to COPY content up rather than
deleting rows.


Sub dontfubar()
Const PATTERN As String = "*COMPANY*" 'adjust to company name

Dim i As Long, j As Long, rng As Range

On Error GoTo CleanUp
Application.Calculation = xlCalculationManual

Set rng = Range("A:H") 'modify as needed - better to reduce this if
possible

i = 0
j = 0
Do While i < rng.Rows.Count
i = i + 1

If rng.Rows(i).Find(what:=PATTERN, lookat:=xlWhole) Is Nothing
Then
j = j + 1
rng.Rows(j).Value2 = rng.Rows(i).Value2
Else
i = i + 9
End If
Loop

i = rng.Rows.Count
If j < i Then rng.Range(rng.Rows(j + 1), rng.Rows(i)).ClearContents

CleanUp:
Application.Calculation = xlCalculationAutomatic

End Sub


Or you could rewrite your formulas to use fixed references like

INDEX(OtherSheet!$A:$A,1,1):INDEX(OtherSheet!$H:$H ,1000,1)

instead of

OtherSheet!$A$1:$H$1000

Here is one of the formulas:
=IF(ISNA(MATCH(1,(RTH!$A$1:$A$127=$A6)*(RTH!$C$1:$ C$127=$M$1),0)),"",INDEX(RTH!$F$1:$F$127,MATCH(1,( RTH!$A$1:$A$127=$A6)*(RTH!$C$1:$C$127=$M$1),0)))

A127, C127 & F127 are the row numbers that are being reduced...


"Shane Devenshire" wrote:

Hi,

One way to handle this is to replace the reference A1:A1000 with
INDIRECT("A1:A1000")
or
OFFSET(A1,0,0,1000)

it would be better if we knew the formula you were using.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Robert" wrote:

Hello,
On sheet 1 I use array formulas that have a set row range. Example
(A1:A1000). Sheet 1 requests date from sheet 2.
On sheet 2 there is data imported from another application that leaves page
header info multiple times, and I use a "cleanup" formula to delete the rows
that contain the unwanted page header info.
I have found that the row deletion on sheet 2 changes the ranges in the
formulas on sheet 1. Example, If the cleanup formula deletes 10 rows on sheet
2, the formula range on sheet 1 changes from A1:A1000 to A1:A990. Everytime I
run it, the range is reduced further. This is the cleanup formula:
Sub DeleteRowsRTH()
FindString = "*COMPANY*" 'adjust to company name
Set b = Range("A:H").Find(what:=FindString, lookat:=xlWhole)
While Not (b Is Nothing)
b.Resize(10).EntireRow.Delete
Set b = Range("A:H").Find(what:=FindString, lookat:=xlWhole)
Wend
End Sub

I don't beleive I can use infinite row designations (A:A) for an array
formula. I tried and get a #NUM error. Is there any way I can lock the
ranges on sheet 1 so they don't change?........or perhaps some other approach
to this problem?
thanks,
Robert

Shane Devenshire wrote...
One way to handle this is to replace the reference A1:A1000 with
INDIRECT("A1:A1000")

Works buu is volatile.

or
OFFSET(A1,0,0,1000)
....

Delete row 1 and see what you get.

This sounds like it might be a better approach.....however, I am getting a
Compile error: syntax error on line:
If rng.Rows(i).Find(what:=PATTERN, lookat:=xlWhole) Is Nothing

R.

"Harlan Grove" wrote:

Robert wrote...
....
I don't beleive I can use infinite row designations (A:A) for an array
formula. I tried and get a #NUM error. Is there any way I can lock the
ranges on sheet 1 so they don't change?........or perhaps some other approach
to this problem?

You could either change your macro to COPY content up rather than
deleting rows.


Sub dontfubar()
Const PATTERN As String = "*COMPANY*" 'adjust to company name

Dim i As Long, j As Long, rng As Range

On Error GoTo CleanUp
Application.Calculation = xlCalculationManual

Set rng = Range("A:H") 'modify as needed - better to reduce this if
possible

i = 0
j = 0
Do While i < rng.Rows.Count
i = i + 1

If rng.Rows(i).Find(what:=PATTERN, lookat:=xlWhole) Is Nothing
Then
j = j + 1
rng.Rows(j).Value2 = rng.Rows(i).Value2
Else
i = i + 9
End If
Loop

i = rng.Rows.Count
If j < i Then rng.Range(rng.Rows(j + 1), rng.Rows(i)).ClearContents

CleanUp:
Application.Calculation = xlCalculationAutomatic

End Sub


Or you could rewrite your formulas to use fixed references like

INDEX(OtherSheet!$A:$A,1,1):INDEX(OtherSheet!$H:$H ,1000,1)

instead of

OtherSheet!$A$1:$H$1000

Hi,

Yes I knew that, but most people put titles on row 1.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Harlan Grove" wrote:

Shane Devenshire wrote...
One way to handle this is to replace the reference A1:A1000 with
INDIRECT("A1:A1000")

Works buu is volatile.

or
OFFSET(A1,0,0,1000)
....

Delete row 1 and see what you get.

Hi,

I don't see anywhere, what version of Excel you are using, 2007 does allow
full column reference in the situation where 2003 would return an error.

Another option is to use the code to enter the formula. For example, just
turn on the recorder, move to each cell with a unique formula and press F2,
Enter. This will record the code for entering the formula. You may want to
tweek the results, for example here is a code sample:

bot = Range("A14").End(xlDown).Row
Range("C14:C" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""CurrQtr""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!AF$2:AF$" & Bottom & ")"
Range("C14:C" & bot) = Range("C14:C" & bot).Value
Range("D14:D" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""NextQtr""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!AF$2:AF$" & Bottom & ")"
Range("D14:D" & bot) = Range("D14:D" & bot).Value
Range("E14:E" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""FutrQtr""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!AF$2:AF$" & Bottom & ")"
Range("E14:E" & bot) = Range("E14:E" & bot).Value
Range("F14:F" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""Past Due""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!AF$2:AF$" & Bottom & ")"
Range("F14:F" & bot) = Range("F14:F" & bot).Value
Range("G14:G" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""Past Due""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!Q$2:Q$" & Bottom & ")"
Range("G14:G" & bot) = Range("G14:G" & bot).Value
Range("H14:H" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""Past Due""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),--('Global Detail'!D$2:D$" & Bottom &
"<""""))"
Range("H14:H" & bot) = Range("H14:H" & bot).Value
Range("B14:B" & bot) = "=SUM(RC[1]:RC[4])"
Range("B14:B" & bot) = Range("B14:B" & bot).Value

Some of these formulas are fairly complex SUMPRODUCT formula working against
30,000 rows but this method works fairly fast, and allow me to covert all the
formulas to values which would otherwise slow down the speed of the
spreadsheet since there are about 3/4 of a million formulas in the file.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Robert" wrote:

Here is one of the formulas:
=IF(ISNA(MATCH(1,(RTH!$A$1:$A$127=$A6)*(RTH!$C$1:$ C$127=$M$1),0)),"",INDEX(RTH!$F$1:$F$127,MATCH(1,( RTH!$A$1:$A$127=$A6)*(RTH!$C$1:$C$127=$M$1),0)))

A127, C127 & F127 are the row numbers that are being reduced...


"Shane Devenshire" wrote:

Hi,

One way to handle this is to replace the reference A1:A1000 with
INDIRECT("A1:A1000")
or
OFFSET(A1,0,0,1000)

it would be better if we knew the formula you were using.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Robert" wrote:

Hello,
On sheet 1 I use array formulas that have a set row range. Example
(A1:A1000). Sheet 1 requests date from sheet 2.
On sheet 2 there is data imported from another application that leaves page
header info multiple times, and I use a "cleanup" formula to delete the rows
that contain the unwanted page header info.
I have found that the row deletion on sheet 2 changes the ranges in the
formulas on sheet 1. Example, If the cleanup formula deletes 10 rows on sheet
2, the formula range on sheet 1 changes from A1:A1000 to A1:A990. Everytime I
run it, the range is reduced further. This is the cleanup formula:
Sub DeleteRowsRTH()
FindString = "*COMPANY*" 'adjust to company name
Set b = Range("A:H").Find(what:=FindString, lookat:=xlWhole)
While Not (b Is Nothing)
b.Resize(10).EntireRow.Delete
Set b = Range("A:H").Find(what:=FindString, lookat:=xlWhole)
Wend
End Sub

I don't beleive I can use infinite row designations (A:A) for an array
formula. I tried and get a #NUM error. Is there any way I can lock the
ranges on sheet 1 so they don't change?........or perhaps some other approach
to this problem?
thanks,
Robert

Hi Shane,
You're right! A:A works on 2007, as I have it at home. That would be my
preferred fix, since it would simplify my formulas on sheet1. Unfortunately,
we have 2003 at work. I'm thinking that I should find a way to delete the
headers on sheet2 without deleting the rows. Given the formula that worked
for deleting rows, can it be changed to just delete the data within the cells
on these rows? This would still allow my copy down process to work without
the headers interferring. Again, here is the formula that worked for the rows:
Sub DeleteRowsRTH()
FindString = "*COMPANY*" 'adjust to company name
Set b = Range("A:H").Find(what:=FindString, lookat:=xlWhole)
While Not (b Is Nothing)
b.Resize(10).EntireRow.Delete
Set b = Range("A:H").Find(what:=FindString, lookat:=xlWhole)
Wend
End Sub

thanks,
Robert

"Shane Devenshire" wrote:

Hi,

I don't see anywhere, what version of Excel you are using, 2007 does allow
full column reference in the situation where 2003 would return an error.

Another option is to use the code to enter the formula. For example, just
turn on the recorder, move to each cell with a unique formula and press F2,
Enter. This will record the code for entering the formula. You may want to
tweek the results, for example here is a code sample:

bot = Range("A14").End(xlDown).Row
Range("C14:C" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""CurrQtr""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!AF$2:AF$" & Bottom & ")"
Range("C14:C" & bot) = Range("C14:C" & bot).Value
Range("D14:D" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""NextQtr""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!AF$2:AF$" & Bottom & ")"
Range("D14:D" & bot) = Range("D14:D" & bot).Value
Range("E14:E" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""FutrQtr""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!AF$2:AF$" & Bottom & ")"
Range("E14:E" & bot) = Range("E14:E" & bot).Value
Range("F14:F" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""Past Due""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!AF$2:AF$" & Bottom & ")"
Range("F14:F" & bot) = Range("F14:F" & bot).Value
Range("G14:G" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""Past Due""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),'Global Detail'!Q$2:Q$" & Bottom & ")"
Range("G14:G" & bot) = Range("G14:G" & bot).Value
Range("H14:H" & bot) = "=SUMPRODUCT(--('Global Detail'!K$2:K$" & Bottom
& "=A14),--('Global Detail'!AD$2:AD$" & Bottom & "=""Past Due""),--('Global
Detail'!A$2:A$" & Bottom & "<""NB""),--('Global Detail'!D$2:D$" & Bottom &
"<""""))"
Range("H14:H" & bot) = Range("H14:H" & bot).Value
Range("B14:B" & bot) = "=SUM(RC[1]:RC[4])"
Range("B14:B" & bot) = Range("B14:B" & bot).Value

Some of these formulas are fairly complex SUMPRODUCT formula working against
30,000 rows but this method works fairly fast, and allow me to covert all the
formulas to values which would otherwise slow down the speed of the
spreadsheet since there are about 3/4 of a million formulas in the file.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Robert" wrote:

Here is one of the formulas:
=IF(ISNA(MATCH(1,(RTH!$A$1:$A$127=$A6)*(RTH!$C$1:$ C$127=$M$1),0)),"",INDEX(RTH!$F$1:$F$127,MATCH(1,( RTH!$A$1:$A$127=$A6)*(RTH!$C$1:$C$127=$M$1),0)))

A127, C127 & F127 are the row numbers that are being reduced...


"Shane Devenshire" wrote:

Hi,

One way to handle this is to replace the reference A1:A1000 with
INDIRECT("A1:A1000")
or
OFFSET(A1,0,0,1000)

it would be better if we knew the formula you were using.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Robert" wrote:

Hello,
On sheet 1 I use array formulas that have a set row range. Example
(A1:A1000). Sheet 1 requests date from sheet 2.
On sheet 2 there is data imported from another application that leaves page
header info multiple times, and I use a "cleanup" formula to delete the rows
that contain the unwanted page header info.
I have found that the row deletion on sheet 2 changes the ranges in the
formulas on sheet 1. Example, If the cleanup formula deletes 10 rows on sheet
2, the formula range on sheet 1 changes from A1:A1000 to A1:A990. Everytime I
run it, the range is reduced further. This is the cleanup formula:
Sub DeleteRowsRTH()
FindString = "*COMPANY*" 'adjust to company name
Set b = Range("A:H").Find(what:=FindString, lookat:=xlWhole)
While Not (b Is Nothing)
b.Resize(10).EntireRow.Delete
Set b = Range("A:H").Find(what:=FindString, lookat:=xlWhole)
Wend
End Sub

I don't beleive I can use infinite row designations (A:A) for an array
formula. I tried and get a #NUM error. Is there any way I can lock the
ranges on sheet 1 so they don't change?........or perhaps some other approach
to this problem?
thanks,
Robert