Last Resort

G

gregork

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
 
K

Ken Wright

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
 
G

gregork

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
 
H

Harlan Grove

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

So you're using Excel 2000 or previous?

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

Tom Ogilvy

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
 
G

gregork

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
 
K

Ken Wright

Just tuck this onto the end of the code:-

Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

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

gregork

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
 
K

Ken Wright

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

gregork

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
 
K

Ken Wright

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

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

End Sub
 
G

gregork

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

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

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

Ken Wright

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

gregork

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

Cheers
greg
 
K

Ken Wright

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

Cheers
greg
Click to expand...
<snip>
 
Top