deleting a row with macro

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

--
Message posted from http://www.ExcelForum.com

0
1/15/2004 3:54:47 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
497 Views

Similar Articles

[PageSpeed] 44

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


---
Message posted from http://www.ExcelForum.com/

0
1/15/2004 4:33:14 AM
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. :

--
Message posted from http://www.ExcelForum.com

0
1/15/2004 1:41:28 PM
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

--
Message posted from http://www.ExcelForum.com

0
1/15/2004 3:04:05 PM
Reply:

Similar Artilces:

Deletion of Vacation Requests
Need a way to track vacation requests that are deleted by employee's through the Business Portal. Possible solution would be to create a status code in the hrm_vacationrequest table for deleted vacation requests instead of removing the data from the table. Clients have requested to report on the deleted vacation request or a way to trigger managers that this event has occurred. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message...

Can I delete an email file from M:?
Is it possible to delete a file/folder from the M: drive directly? Will exchange be able to track the changes? Thanks for the help ahead of time. That is not a good idea�I Strong recomend you do not directly access Exchagne IFS(M:) -- Jammy "ctrlgrid" <jkit001@yahoo.com> ���g��l��s�D :4f9d64d8.0410252347.662bbc48@posting.google.com... > Is it possible to delete a file/folder from the M: drive directly? > Will exchange be able to track the changes? > > Thanks for the help ahead of time. It is possible to do so. Not sure what you mean by will Exchange track ...

Deleting "hidden" charts on a worksheet
Hi everyone, I've been playing around w/ a worksheet that contains data and charts, inserting and removing multiple rows at a time. Unfortunately, I did not change the properties of the charts and they were all set to "Move and Size w/ Cells". When I deleted a series of rows, these charts disappeared and left a line across the spreadsheet where they used to be. I've tried everything to get rid of them (because they are now calculating incorrectly and giving me error messages), including deleting the rows where they appear, trying to select them on the sheet, but nothing w...

Pivot chart and Total row
Hi All I have a Pivot table and attached Pivot chart (all good so far). However, there are three columns (drop column details here) which are the two columns of data and a grand total column (showing the average of my two data columns) When I turn it into a pivot chart it does not display the grand total column as well (is there a way to do this). I have tried to go into the chart options but these are locked. I can copy and paste the results but this is a bit clumsy. Any suggestions - any help greatly appreciated Kind regards Rexmann PS using excel 2003 ...

Maintaining the selected row after sorting an ASP.NET DataGridView
How can I maintain the same selected row even after a sort on a DataGridView with multiple pages? I have been looking for an example for weeks and still no luck, so help would be appreciated. Thanks Hi, >How can I maintain the same selected row even after a sort on a DataGridView >with multiple pages? >I have been looking for an example for weeks and still no luck, so help >would be appreciated. >Thanks As far as I know there's no DataGridView control in ASP.NET. I assume you mean GridView. You may refer to the following code. If you mean DataGr...

Accidentally deleted a store!
One of our people accidentally deleted a store from HQ Manager. When I do SQL queries I can see that the data is still there, but obviously we need to get everything back to normal. The last good SQL backup appears to be a week old (stupid tapes) and we don't have ANY good bck backups for POS. I've cut off that store from updating until I know something useful. So what am I going to have to do to recover this? I have almost no idea where to go from here. Thanks for any info you can post. -Mark L whoah. that's bad. i don't know, it's a long shot, but perhaps if you...

Deleting Text
I know that with "&" I can add two text cells together. How do I get rid of text? In the example "Roddick, Andy (USA) " in A2, I want to get rid of "(USA) ". I can separate with "=right(A1,7)", the last seven characters, but how to "subtract" them from within A2" Felix In an un-used cell, enter: =LEFT(A2,LEN(A2)-7) then copy this cell and paste/special/value back onto A2 -- Gary''s Student - gsnu200827 "Felix" wrote: > I know that with "&" I can add two text cells together. > > How...

Deleted Items Retention #3
I have a question about the Deleted items retention period. When a user highlights messages from their inbox and does a shift delete without sending them to the deleted items folder, are they recoverable with the deleted itmems retention feature with Exchange by going to tools recover deleted items whithin Outlook? Thanks in advance. Chad This may help shed some more light on Deleted Item Retention, including your particular question about shift-delete. http://support.microsoft.com/kb/249680 -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blo...

How do I delete chart superimposed on Excel document?
Created Excel document. Print preview did not show gridlines. In attempting to get gridlines, somehow got into charts. A chart was created and is superimposed on original document. How do I get rid of the chart? How do I get gridlines on to original document? Oz Right-click on the chart and Edit>Clear>All To print gridlines.................File>Page Setup>Sheet Check "gridlines" and uncheck "draft quality". Gord Dibben MS Excel MVP On Fri, 4 Aug 2006 14:27:01 -0700, Oz Osborn <Oz Osborn@discussions.microsoft.com> wrote: >Created Excel docume...

Importing Data In A Macro
I need to use Access a little like an ETL tool, and want to run a macro that imports data from an ODBC source (Sybase in this case) and then uses insert and update queries in Access for manipulation. The macro will be scheduled using the Windows Scheduler. Anyway, I am trying to use the "Transfer Database" action in the macro. The only thing I don't understand, is how do I keep from importing the entire table from Sybase, i.e. I want to import using a query, looking at the Access database, getting a maximum existing date and then just importing anything newer than that. The Syba...

How to copy rows from Excel without losing the row height format
In Excel 2003. I have a section of a spreadsheet that contains a 25 rows that each have different row height. I want to copy these to a section below, but lose the row height formatting. How do I copy without losing the row height format? Are you copying the entire row? Also, don't forget the format painter and the right-click paste special options. ************ Anne Troy www.OfficeArticles.com "artex boy" <artex boy@discussions.microsoft.com> wrote in message news:4DA6D00F-8957-4569-94C5-EDB2AE6F683D@microsoft.com... > In Excel 2003. I have a section of a sp...

Excel 2000... Missing row and column designators.
Ho do you mean 'Tools - Options - View' and check 'Row/Column headings' -- Regards Frank Kabel Frankfurt, Germany "Clarence" <carowe@cnyconnect.net> schrieb im Newsbeitrag news:2ee7401c46c21$a45aef40$a501280a@phx.gbl... > ...

Why are my columns and rows reversed when I open Excel
When I open my Excel spreadsheet the columns, rows and scroll bars are reversed?? What's happened? Tools|options|International Tab Uncheck that "view current sheet right-to-left" Deb Web wrote: > > When I open my Excel spreadsheet the columns, rows and scroll bars are > reversed?? What's happened? -- Dave Peterson ...

Deleting Payees?
Do any of you delete your payees in your money file? I exported the "Transactions by Payee" report to Excel and determined that I have nearly 1300 unique payees, many of which are of the form: BKOFAMERICA ATM #23236538 BKOFAMERICA ATM #43882303 I can go into Categories & Payees and move all of these into a single "Bkofamerica Atm" payee, but I can only do this one by one. The reason that I ask is that occasionally, I need to search for something by payee name and the list of payees is extraordinarily long. How do all of you deal with this? Ignore it? Delete the...

unable to delete Windows.edb file
I am trying to delete my Windows.edb file which is in C:\Documents and Settings\All Users\Application Data\Microsoft\Search\Data\Applications\Windows I have indexing service set to manual and off. I also have Microsoft Search disabled. But when I try to delete the Windows.edb file, I get a file in use message and it won't delete. What else uses it? I am also getting a bungh of MSSBC4xx.log files that show up in the same folder. There are about 50 of them now. Where do they come from? And is it related to my being unable to delete the ..edb file? Thanks, ...

Macro to find a row and delete it and the 9 rows above it. #2
Thanks alot that did the trick. This forum and you guys are life saver -- Rusty_n ----------------------------------------------------------------------- Rusty_nl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=671 View this thread: http://www.excelforum.com/showthread.php?threadid=31376 ...

Replacing a subdirectory name in a macro
I am currently running a macro that every time I run the macro I have t replace the name of a subdirectory that a file is saving to. Is ther anyway I can name a cell in ecel that has the subdiectory's name store in it, and get the macro to accept that name in the address that th file is being saved to -- Message posted from http://www.ExcelForum.com Hi, Dim TodaysFolder as String TodaysFolder=cells(1,1).value 'cell A1. You should add the workbook & sheet names to make 'sure it's the right one ThisWorkbook.SaveAs "c:\thisfolder\andthatfloder\" & TodaysFolde...

Macro Action Fails on Duplicate Record
I have a form with a subform whose visibile property is set to no. I have a macro that runs when a choice is made in a combo box that sets the visible property of the subform to yes when the selection=2. The macro sets the visible property of the subform to no when the selection does not =2. This is how I have the macro: Condition: [Appearance]=2 Action:Set Value Item: [Forms]![Appearance Request Basic]![OffSiteApp].[Form].[Detail].[Visible] Expression: True Condition:... Action:StopMacro Condition:(this I left blank) Action: SetValue Item:[Forms]![Appearance Request Basic]![OffSiteApp...

Macro inside another macro?
I like to refer to another macro within another macro. Is this possible? Does this work? sub macro1() .... .... sub Macro2() .... .... end sub ..... ..... end sub ? Yes this does work. >-----Original Message----- >I like to refer to another macro within another macro. > >Is this possible? >Does this work? > >sub macro1() >.... >.... > > sub Macro2() > .... > .... > end sub > >..... >..... >end sub >? > >. > thank you >-----Original Message----- >Yes this does work. > > >...

How not to display any pop up message for macro?
When macro imports an external links into sheets, it pops up a message is about the pasting area is larger then worksheet for confirmation, does anyone have any suggestions on how not to display any pop up message for running macro? Thanks in advance for any suggestions qryConnect = "URL;http://www.mmacycles.com" Set anySheet = Sheets("MMA") With anySheet.QueryTables.Add(Connection:=qryConnect, Destination _ :=anySheet.Range("$A$1")) .Name = "o070104" .FieldNames = True .RowNumbers = False .Fil...

Need Unprotect/Protect for Excel Macro
Hello there, I need the code to put into a macro to go in and unprotect a sheet (just long enough for me to insert some data)...then re-protect the sheet before saving. This way....the user cannot destroy the data or formula in teh protected sheet. How do I do this? Is there a global command or do I have to imput my password when I ask it to follow me recording the macro. Please help! Anyone!! Thanks!! Wade Previous post of J E McGimpseys will give you the syntax Sub Toggleprotect2() Const PWORD As String = "ken" Dim wkSht As Worksheet For Each sh ...

how do i "de-dupe?" rows by a column value?
I have a complex operation to perform. My spreadsheet has rows that indicate items (control number in column A) linked to titles (control number in column B). A title can have multiple items in the spredsheet. I want to number the titles and assign random numbers to them so that I can get two random samples. I also want to have my random numbering retained in the spreadsheet as a whole, so I can see all the items assigned to my samples. How can I "dedupe" column B, assign a random number to each title control, then transfer that data to my "undeduped" spreadshe...

How do I identify and delete macros
Before I got Windows XP, I used a macro I found through this site to set up a couple of documents to print as a booklet. With the new Office software, I don't need that macro anymore. Some of the documents I use are based on older documents containing the macro (in both Word and Publisher), and I get a dialog box asking me to enable or disable the macros. I would like to take out the macros all together, but I don't know how. Also, for all I know (which isn't much at all about programming), there may be macros built in to the program that I don't want to delete. How do I...

Deleted items from shared mailbox
We have a mailbox (LoanAdmin) shared among 6 users. Information is emailed to LoanAdmin@OurCompany.com. Each user opens the mail box, can read, and delete messages. Problem is, when messages are deleted, they go to the Deleted Items folder in that user's mailbox. Is there a way to make them go to the Deleted Items in LoanAdmin so that their supervisor can see what was deleted? Outlook versions are 2000 and 2003 The server is an Exchange server on a Win2K server ...

How to delete duplicated records. Each record has four lines
I would like to delete duplicated records from an excel file. Each record has four lines. Thanks. Arcesio, You'll find that none of Excel's tools (Sort, Pivot Tables, Database functions, Subtotals, Charts, Autofilter, Advanced filter, Data form, to name a few), work with multiline records. I think you should look strongly at converting your data to a bona fide database-style table. It will open up all kinds of Excel functionality. -- Earl Kiosterud www.smokeylake.com "Arcesio Hernandez" <Arcesio Hernandez@discussions.microsoft.com> wrote in message news:7767...