delete row formula changes array range on related sheet

R

Robert

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
 
S

Shane Devenshire

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.
 
H

Harlan Grove

Robert said:
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?
Click to expand...

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
 
R

Robert

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...
 
R

Robert

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.
 
S

Shane Devenshire

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.
 
R

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dependent drop down dynamic range formula, off sheet. 7
Drop Down with dynamic named range offsheet source list 14
Auto Expanding Range for array-entered IF 3
Array code only works on selected sheet 14
Using SUM + INDEX in array formula 5
Formula Based on Multiple Sheets 5
Named Range that uses "relative" range - Possible? 5
Array VLOOKUP lookup_Value does not increment 3

Top