Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
delete row formula changes array range on related sheet
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
different formula for ROW, array / range? | Excel Discussion (Misc queries) | |||
Sum.if criteria related to range of cells | Excel Worksheet Functions | |||
Range related for chart | Excel Discussion (Misc queries) | |||
Delete range on another sheet when checkbox is unchecked | Excel Discussion (Misc queries) | |||
Array Formula, noncontigous range | Excel Worksheet Functions |