deleting a row with macro

  • Thread starter Thread starter clayton
  • Start date Start date
C

clayton

NOOBIE here...
Here's what Ive got.
I am trying to create a "Delete Vendor" button

I know there is a much better way to do this..

On one sheet is the linked cell of a combobox.
I am selecting that cell, copying and switching to the data sheet.
There I want to find the copied cell's value, select that complete ro
and delete it. (Only the content, not the row itself because I wil
have a sort run afterward and it is an array so I want to keep th
size.)

My problem is that "Rows(ActiveCell.Row).Select" is executing befor
the find dialog box has time to appear effectivly defeating the whol
process.

Sub Macro5()


Sheets("master").Select
Range("C14:F14").Select
ActiveCell.Copy
Sheets("Data").Select
Range("A1").Select



SendKeys ("^f")

SendKeys ("^v")

SendKeys "{ENTER}"

SendKeys "{ESC}"


Rows(ActiveCell.Row).Select

ActiveCell.EntireRow.Delete
Sheets("master").Select
SendKeys "{ESC}"
End Su
 
clayton,

try these two.

Macro5 will only delete the first found instance on the data sheet,
Macro5_A will delete all instances in the data sheet.

Sub Macro5()
If UCase(ActiveCell.Parent.Name) <> "MASTER" Then Exit Sub
Dim FindValue
Dim Result As Range


FindValue = ActiveCell.Value

Set Result = Sheets("Data").Cells.Find(what:=FindValue)
If Not Result Is Nothing Then
Result.EntireRow.Delete
End If


End Sub



Sub Macro5_A()
If UCase(ActiveCell.Parent.Name) <> "MASTER" Then Exit Sub
Dim FindValue
Dim Result As Range
Dim firstAddress As String

FindValue = ActiveCell.Value

With Sheets("Data").Cells
Set Result = .Find(what:=FindValue)
If Not Result Is Nothing Then
firstAddress = Result.Address
Do
Result.EntireRow.Delete
Set Result = .FindNext
Loop While Not Result Is Nothing And Result.Address <>
firstAddress
End If
End With

End Sub
 
Thanks for the quick reply.
The first one (delete one) worked great after I selected the cell c14.
The second one didnt go as smoothly. Here is what I got.

I assigned the macro to a form button. When I click it it does no
delete anything off of the data sheet. Actually it looks as though i
is exiting right away.
I commented out the line
If UCase(ActiveCell.Parent.Name) <> "master" Then Exit Sub
and it then did delete from the data sheet. BUT, I got an "run-tim
error "91": Object varialbe or With block variable not set." error.

I had to manually select cell c14 on the master sheet for it to work a
all.

I can use the first one with no problem. But I will check back to se
if there was a fix for the second one just in case. :
 
Ok, It is now deleting the rows just fine. I have one problem though.
do not want to change the size or the array when deleting. Can
instead just erase the data in a row and not delete the row itself
Here is the userform code I ended up with.

Private Sub CommandButton1_Click()


If UCase(ActiveCell.Parent.Name) <> "MASTER" Then Exit Sub
Sheets("master").Select
Range("C14:F14").Select


Dim FindValue
Dim Result As Range


FindValue = ActiveCell.Value

Set Result = Sheets("Data").Cells.Find(what:=FindValue)
If Not Result Is Nothing Then
Result.EntireRow.Delete
End If
Unload Me

End Sub


Private Sub CommandButton2_Click()
End
End Su
 
Back
Top