OK, I think I know what you mean, in that the last line now takes out every row
within the usedrange that is blank in Col A, but even if I fix that, I don't see
how you are maintaining your formulas anyway, although to be fair I'm also not
sure if the 'details' sheet is any of the ones we have referred to as Sheet1 or
Sheet2. If I assume that Sheet2 is your 'Details' Sheet, then the ranges within
them must be changing when you delete rows, and the number of formulas you have
must be reducing anyway, whichever way you do it, including the one you
detailed.
The following code will limit the range to the area within Column A that has
data, so it won't interfere with rows below that, but just for example, using
the formula given in your note of
=SUMIF('Details'!A$2:A$1909,$A2,'Details'!E$2:E$19 09)
Even using your method, if I then delete say two rows, then all of the formulas
in Col F would have the range change so that they now said
=SUMIF('Details'!A$2:A$1907,$Axx,'Details'!E$2:E$1 907), ie 1907 instead of
1909, and you would have two less rows of formulas.
Anyway, I may just have misunderstood, but try this anyway:-
Sub FindRep()
Dim Sht1 As Worksheet
Dim Sht2 As Worksheet
Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long
Dim LastRow As Long
Set Sht1 = Sheets("Sheet1")
Set Sht2 = Sheets("Sheet2")
With Sht2
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = .Range(Cells(1, "A"), Cells(LastRow, "A"))
End With
findme = Sht1.Range("A1").Value
repwith = ""
If findme = "" Then Exit Sub
cnt = Application.WorksheetFunction.CountIf(Rng, findme)
If cnt = 0 Then
MsgBox "There are no instances of that value in your data"
Exit Sub
End If
Rng.Replace What:=findme, Replacement:=repwith, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
End Sub
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
"gregork" wrote in message
...
Hi Ken thanks for sticking with me on this....I'll try to clarify
things:......In Sheet2 E2 I have a formula:D2&B2
.......In Sheet2 F2 I have a formula:SUMIF('
Details'!A$2:A$1909,$A2,'Details'!E$2:E$1909)
I have dragged these formulas all the way down the columns..so new entries
will be calculated instantly.
Now when I delete the entire row I don't want to lose the formulas that are
filled down the columns. It would be like "manually" right clicking the row
number ( highlighting the entire row), selecting delete and then the row is
removed and everything shifts up....when I do this on my sheet I don't lose
the formulas I have filled down the columns. I want to delete the row
without losing my formulas I have filled down the columns.
regards
greg
"Ken Wright" wrote in message
...
Snipped to cut the thread size - Continue from this one please
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--
<snip
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.718 / Virus Database: 474 - Release Date: 09/07/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.718 / Virus Database: 474 - Release Date: 09/07/2004