I have been searching through google groups to try and find a previous post
that would help solve my problem but to no avail. With a command button
click event I want to delete all values in the range Sheet2 A:A that equal
the value in the cell Sheet1 A1. Seems simple enough but I can't get it
working. I have a code that works for looking up a combobox value but when I
modify it for a cell value it fails.

greg

How about something like this:-

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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, SearchFormat:=False, _
ReplaceFormat:=False

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
...
I have been searching through google groups to try and find a previous post
that would help solve my problem but to no avail. With a command button
click event I want to delete all values in the range Sheet2 A:A that equal
the value in the cell Sheet1 A1. Seems simple enough but I can't get it
working. I have a code that works for looking up a combobox value but when I
modify it for a cell value it fails.

greg






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

Thanks for the reply Ken. I have tried your code and I'm getting a compile
error: named argument not found........for " SearchFormat:="

Regards
gregorK

"Ken Wright" wrote in message
...
How about something like this:-

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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, SearchFormat:=False, _
ReplaceFormat:=False

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
...
I have been searching through google groups to try and find a previous
post
that would help solve my problem but to no avail. With a command button
click event I want to delete all values in the range Sheet2 A:A that
equal
the value in the cell Sheet1 A1. Seems simple enough but I can't get it
working. I have a code that works for looking up a combobox value but
when I
modify it for a cell value it fails.

greg






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

"gregork" wrote...
Thanks for the reply Ken. I have tried your code and I'm getting
a compile error: named argument not found........for " SearchFormat:="
....

So you're using Excel 2000 or previous?

Delete the SearchFormat:=... and ReplaceFormat:=... parameters.

So remove it. It was an argument added in a version of excel later than
yours.

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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

End Sub

--
Regards,
Tom Ogilvy


"gregork" wrote in message
...
Thanks for the reply Ken. I have tried your code and I'm getting a compile
error: named argument not found........for " SearchFormat:="

Regards
gregorK

"Ken Wright" wrote in message
...
How about something like this:-

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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, SearchFormat:=False, _
ReplaceFormat:=False

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
...
I have been searching through google groups to try and find a previous
post
that would help solve my problem but to no avail. With a command
button
click event I want to delete all values in the range Sheet2 A:A that
equal
the value in the cell Sheet1 A1. Seems simple enough but I can't get
it
working. I have a code that works for looking up a combobox value but
when I
modify it for a cell value it fails.

greg






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

Serves me right for not trying it in 2K - Cheers Harlan :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Harlan Grove" wrote in message
...
"gregork" wrote...
Thanks for the reply Ken. I have tried your code and I'm getting
a compile error: named argument not found........for " SearchFormat:="
...

So you're using Excel 2000 or previous?

Delete the SearchFormat:=... and ReplaceFormat:=... parameters.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

<slaps himself for not trying it in 2K Cheers Tom

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Tom Ogilvy" wrote in message
...
So remove it. It was an argument added in a version of excel later than
yours.

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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

End Sub

--
Regards,
Tom Ogilvy


"gregork" wrote in message
...
Thanks for the reply Ken. I have tried your code and I'm getting a compile
error: named argument not found........for " SearchFormat:="

Regards
gregorK

"Ken Wright" wrote in message
...
How about something like this:-

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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, SearchFormat:=False, _
ReplaceFormat:=False

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
...
I have been searching through google groups to try and find a previous
post
that would help solve my problem but to no avail. With a command
button
click event I want to delete all values in the range Sheet2 A:A that
equal
the value in the cell Sheet1 A1. Seems simple enough but I can't get
it
working. I have a code that works for looking up a combobox value but
when I
modify it for a cell value it fails.

greg






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

Thanks for the help guys. Sorry I should have mentioned I was using 2000.
One other thing I should have mentioned is that I would prefer to delete
the entire row for the value found in the range Sheet A:A not just the
cell. A rather important bit of information I missed out in the original
post I know. "It's easier to beg forgiveness than ask permission :-)"

I guess I have to throw in a EntireRow.Delete somewhere right?

Regards
Greg


"Ken Wright" wrote in message
...
<slaps himself for not trying it in 2K Cheers Tom

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--



"Tom Ogilvy" wrote in message
...
So remove it. It was an argument added in a version of excel later than
yours.

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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

End Sub

--
Regards,
Tom Ogilvy


"gregork" wrote in message
...
Thanks for the reply Ken. I have tried your code and I'm getting a
compile
error: named argument not found........for " SearchFormat:="

Regards
gregorK

"Ken Wright" wrote in message
...
How about something like this:-

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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, SearchFormat:=False, _
ReplaceFormat:=False

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
...
I have been searching through google groups to try and find a
previous
post
that would help solve my problem but to no avail. With a command
button
click event I want to delete all values in the range Sheet2 A:A
that
equal
the value in the cell Sheet1 A1. Seems simple enough but I can't
get
it
working. I have a code that works for looking up a combobox value
but
when I
modify it for a cell value it fails.

greg






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004

Just tuck this onto the end of the code:-

Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delet e

It lets you run through all the replaces, and then in one fell swoop will delete
all rows within Rng that have a blank in Column A. Deleting as you went along
would probably take longer, so sweeping up at the end is probably preferable
here. Only caveat is that ALL blanks will go, so if there are any other
legitimate blanks in that data, do NOT use this. If this is the case, then come
back and we'll do it another way.

--
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
...
Thanks for the help guys. Sorry I should have mentioned I was using 2000.
One other thing I should have mentioned is that I would prefer to delete
the entire row for the value found in the range Sheet A:A not just the
cell. A rather important bit of information I missed out in the original
post I know. "It's easier to beg forgiveness than ask permission :-)"

I guess I have to throw in a EntireRow.Delete somewhere right?

Regards
Greg


"Ken Wright" wrote in message
...
<slaps himself for not trying it in 2K Cheers Tom

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--



"Tom Ogilvy" wrote in message
...
So remove it. It was an argument added in a version of excel later than
yours.

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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

End Sub

--
Regards,
Tom Ogilvy


"gregork" wrote in message
...
Thanks for the reply Ken. I have tried your code and I'm getting a
compile
error: named argument not found........for " SearchFormat:="

Regards
gregorK

"Ken Wright" wrote in message
...
How about something like this:-

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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, SearchFormat:=False, _
ReplaceFormat:=False

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
...
I have been searching through google groups to try and find a
previous
post
that would help solve my problem but to no avail. With a command
button
click event I want to delete all values in the range Sheet2 A:A
that
equal
the value in the cell Sheet1 A1. Seems simple enough but I can't
get
it
working. I have a code that works for looking up a combobox value
but
when I
modify it for a cell value it fails.

greg






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/07/2004








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/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

Thanks Ken, your suggestion works very well but... I have formulas in the
columns F:F and E:E on Sheet2 and they are removed with code. I want to
delete the row without losing the formulas.

Many thanks
greg

"Ken Wright" wrote in message
...
Just tuck this onto the end of the code:-

Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delet e

It lets you run through all the replaces, and then in one fell swoop will
delete
all rows within Rng that have a blank in Column A. Deleting as you went
along
would probably take longer, so sweeping up at the end is probably
preferable
here. Only caveat is that ALL blanks will go, so if there are any other
legitimate blanks in that data, do NOT use this. If this is the case,
then come
back and we'll do it another way.

--
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
...
Thanks for the help guys. Sorry I should have mentioned I was using
2000.
One other thing I should have mentioned is that I would prefer to
delete
the entire row for the value found in the range Sheet A:A not just the
cell. A rather important bit of information I missed out in the original
post I know. "It's easier to beg forgiveness than ask permission :-)"

I guess I have to throw in a EntireRow.Delete somewhere right?

Regards
Greg


"Ken Wright" wrote in message
...
<slaps himself for not trying it in 2K Cheers Tom

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--



"Tom Ogilvy" wrote in message
...
So remove it. It was an argument added in a version of excel later
than
yours.

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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

End Sub

--
Regards,
Tom Ogilvy


"gregork" wrote in message
...
Thanks for the reply Ken. I have tried your code and I'm getting a
compile
error: named argument not found........for " SearchFormat:="

Regards
gregorK

"Ken Wright" wrote in message
...
How about something like this:-

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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,
SearchFormat:=False, _
ReplaceFormat:=False

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
...
I have been searching through google groups to try and find a
previous
post
that would help solve my problem but to no avail. With a
command
button
click event I want to delete all values in the range Sheet2
A:A
that
equal
the value in the cell Sheet1 A1. Seems simple enough but I
can't
get
it
working. I have a code that works for looking up a combobox
value
but
when I
modify it for a cell value it fails.

greg






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date:
02/07/2004








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/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

LOL - Talk about wanting your cake and eating it :-) You are going to have to
clarify the requirements here. You said you wanted to delete the entire row,
but by default you will obviously lose anything on those rows, regardless of
what column they are in, be it B, C, D, E, F etc. Does this mean you don't want
to delete the rows, or you just want to delete the cells in Column A or what?
If you just delete the cells then I assume you would want data in remaining rows
to shift up, but that will then possibly screw up other formulas referring to
Col A.

You really need to define exactly what it is you need, and also tell us what is
in the other columns so we can anticipate the impact of what we give you.

--
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
...
Thanks Ken, your suggestion works very well but... I have formulas in the
columns F:F and E:E on Sheet2 and they are removed with code. I want to
delete the row without losing the formulas.

Many thanks
greg

"Ken Wright" wrote in message
...
Just tuck this onto the end of the code:-

Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delet e

It lets you run through all the replaces, and then in one fell swoop will
delete
all rows within Rng that have a blank in Column A. Deleting as you went
along
would probably take longer, so sweeping up at the end is probably
preferable
here. Only caveat is that ALL blanks will go, so if there are any other
legitimate blanks in that data, do NOT use this. If this is the case,
then come
back and we'll do it another way.

--
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
...
Thanks for the help guys. Sorry I should have mentioned I was using
2000.
One other thing I should have mentioned is that I would prefer to
delete
the entire row for the value found in the range Sheet A:A not just the
cell. A rather important bit of information I missed out in the original
post I know. "It's easier to beg forgiveness than ask permission :-)"

I guess I have to throw in a EntireRow.Delete somewhere right?

Regards
Greg


"Ken Wright" wrote in message
...
<slaps himself for not trying it in 2K Cheers Tom

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--



"Tom Ogilvy" wrote in message
...
So remove it. It was an argument added in a version of excel later
than
yours.

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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

End Sub

--
Regards,
Tom Ogilvy


"gregork" wrote in message
...
Thanks for the reply Ken. I have tried your code and I'm getting a
compile
error: named argument not found........for " SearchFormat:="

Regards
gregorK

"Ken Wright" wrote in message
...
How about something like this:-

Sub FindRep()

Dim Rng As Range
Dim cnt As Long
Dim findme As Variant
Dim repwith As Variant
Dim rep As Long

Set Rng = Sheets("Sheet2").Columns("A:A")
findme = Sheets("Sheet1").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,
SearchFormat:=False, _
ReplaceFormat:=False

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
...
I have been searching through google groups to try and find a
previous
post
that would help solve my problem but to no avail. With a
command
button
click event I want to delete all values in the range Sheet2
A:A
that
equal
the value in the cell Sheet1 A1. Seems simple enough but I
can't
get
it
working. I have a code that works for looking up a combobox
value
but
when I
modify it for a cell value it fails.

greg






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date:
02/07/2004








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02/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






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

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

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

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

Hi Ken , Thanks for your reply. I've run into a hitch with the following
line:

Set Rng = .Range(Cells(1, "A"), Cells(LastRow, "A"))

I get the msg:" Run-time Error '1004'
Method 'Range' of object'_Worksheet failed

Many Thanks
greg


"Ken Wright" wrote in message
...
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

LOL - Damned unqualified ranges - I assume you are calling or running this from
another sheet.

Try this:-

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

With Rng
.Replace What:=findme, Replacement:=repwith, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

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 your reply. I've run into a hitch with the following
line:

Set Rng = .Range(Cells(1, "A"), Cells(LastRow, "A"))

I get the msg:" Run-time Error '1004'
Method 'Range' of object'_Worksheet failed

<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

Many thanks Ken . I now have everything how I want it. I really appreciate
patience your perseverance.

Cheers
greg

"Ken Wright" wrote in message
...
LOL - Damned unqualified ranges - I assume you are calling or running this
from
another sheet.

Try this:-

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

With Rng
.Replace What:=findme, Replacement:=repwith, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

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 your reply. I've run into a hitch with the following
line:

Set Rng = .Range(Cells(1, "A"), Cells(LastRow, "A"))

I get the msg:" Run-time Error '1004'
Method 'Range' of object'_Worksheet failed

<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

You're welcome :-)

--
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
...
Many thanks Ken . I now have everything how I want it. I really appreciate
patience your perseverance.

Cheers
greg
<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