Simple deletion macro

Hi,

Is there any simpler (than below) method of deleting a bunch of rows based
on some criteria? (in this case, empty cells). I run the macro below, but it
takes forever to execute. Thanks,

Best regards
Yarroll

Dim i As Long, ile As Long
ile = WorksheetFunction.CountA(ActiveSheet.Range("B:B"))
For i = ile To 1 Step -1
If Cells(i, 1).Value = "" Then
Rows(i).Delete
End If
Next
End Sub


0
2/16/2004 6:36:24 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
323 Views

Similar Articles

[PageSpeed] 2

Yarroll,

Use Auto-filter on column B, and select a criteria of non-blanks. Delete all
visible rows, with a blue row number to the left.
-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Yarroll" <yarroll999@wytnijtopoczta.onet.pl> wrote in message
news:c0r2jp$d5b$1@news.onet.pl...
> Hi,
>
> Is there any simpler (than below) method of deleting a bunch of rows based
> on some criteria? (in this case, empty cells). I run the macro below, but
it
> takes forever to execute. Thanks,
>
> Best regards
> Yarroll
>
> Dim i As Long, ile As Long
> ile = WorksheetFunction.CountA(ActiveSheet.Range("B:B"))
> For i = ile To 1 Step -1
> If Cells(i, 1).Value = "" Then
> Rows(i).Delete
> End If
> Next
> End Sub
>
>


0
bob.phillips1 (6510)
2/16/2004 6:42:45 PM
Sorry, meant blanks not non-blanks.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23T5fsyL9DHA.2316@TK2MSFTNGP11.phx.gbl...
> Yarroll,
>
> Use Auto-filter on column B, and select a criteria of non-blanks. Delete
all
> visible rows, with a blue row number to the left.
> -- 
>
> HTH
>
> Bob Phillips
>     ... looking out across Poole Harbour to the Purbecks
> (remove nothere from the email address if mailing direct)
>
> "Yarroll" <yarroll999@wytnijtopoczta.onet.pl> wrote in message
> news:c0r2jp$d5b$1@news.onet.pl...
> > Hi,
> >
> > Is there any simpler (than below) method of deleting a bunch of rows
based
> > on some criteria? (in this case, empty cells). I run the macro below,
but
> it
> > takes forever to execute. Thanks,
> >
> > Best regards
> > Yarroll
> >
> > Dim i As Long, ile As Long
> > ile = WorksheetFunction.CountA(ActiveSheet.Range("B:B"))
> > For i = ile To 1 Step -1
> > If Cells(i, 1).Value = "" Then
> > Rows(i).Delete
> > End If
> > Next
> > End Sub
> >
> >
>
>


0
bob.phillips1 (6510)
2/16/2004 6:47:37 PM
Yarroll,

Here's a VBA method of this as well

Sub DeleteRows()
    With ActiveSheet
        .Range("B1").EntireRow.Insert
        .Range("B1").FormulaR1C1 = "Test"
        .Columns("B:B").AutoFilter Field:=1, Criteria1:="="
        .Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Range("B1").EntireRow.Delete
    End With
End Sub

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23T5fsyL9DHA.2316@TK2MSFTNGP11.phx.gbl...
> Yarroll,
>
> Use Auto-filter on column B, and select a criteria of non-blanks. Delete
all
> visible rows, with a blue row number to the left.
> -- 
>
> HTH
>
> Bob Phillips
>     ... looking out across Poole Harbour to the Purbecks
> (remove nothere from the email address if mailing direct)
>
> "Yarroll" <yarroll999@wytnijtopoczta.onet.pl> wrote in message
> news:c0r2jp$d5b$1@news.onet.pl...
> > Hi,
> >
> > Is there any simpler (than below) method of deleting a bunch of rows
based
> > on some criteria? (in this case, empty cells). I run the macro below,
but
> it
> > takes forever to execute. Thanks,
> >
> > Best regards
> > Yarroll
> >
> > Dim i As Long, ile As Long
> > ile = WorksheetFunction.CountA(ActiveSheet.Range("B:B"))
> > For i = ile To 1 Step -1
> > If Cells(i, 1).Value = "" Then
> > Rows(i).Delete
> > End If
> > Next
> > End Sub
> >
> >
>
>


0
bob.phillips1 (6510)
2/16/2004 6:51:20 PM
Bob, - thanks a million. Best regards, Y.

<bob.phillips@notheretiscali.co.uk> napisa� w wiadomo�ci
news:uHNPf3L9DHA.1112@tk2msftngp13.phx.gbl...
> Yarroll,
>
> Here's a VBA method of this as well
>
> Sub DeleteRows()

(snip)


0
2/16/2004 7:30:09 PM
Reply:

Similar Artilces:

Pre-Addressed Message Macro
I'm new here, so please forgive me if this has been addressed before. I am using Outlook 2003 running on Windows XP. I would like to create a macro that I could put on a toolbar button that will automatically open an email message already addressed and already have the standard information in the subject line. I know nothing about VB, and Outlook won't let me record a ketstroke macro to do this. Thanks for your help. -CB google@cyberbilly.net <google@cyberbilly.net> wrote: > I am using Outlook 2003 running on Windows XP. > > I would like to create a macro that I ...

Deleting multiple attachments in a 2003 message
In Outlook 2000, when you received an email with 20 attachments, you could select them all and click the delete button to remove them from the message at one time. In Outlook 2003, the only way seems to be by clicking each attachment individually, right clicking it and the clicking remove off of the menu one at a time. Is there an easier way? Jeffrey L ...

Macro to send email automatically.
I have a macro set up to send an email to users when there password is due to expire. How do i get the email to actually send. The mail is populated and ready to send but wont actually go. Is there a line i need to add at the end of the macro to send the mail? Show your code Which mail program do you use ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dan Wood" <DanWood@discussions.microsoft.com> wrote in message news:A3452486-D1B7-4D5A-BE4B-480119AAAE0A@microsoft.com... >I have a macro set up to send an email to users when there passw...

Using macros in Excel XP that were created in earlier vers
I am having trouble opening some of my excel documents created in earlier (97 & 2000) versions of Excel, now that I have Windows XP and Office XP 2002. What do I need to do? Also, my macros won't work now either? Thelma You should not be experiencing problems with compatibility across these versions you mention. "having trouble" and "won't work" are quite vague phrases. Could you give more info and pershaps an example or two. Gord Dibben Excel MVP - XL97 SR2 & XL2002 On Tue, 15 Jul 2003 16:48:28 -0700, "Thelma Faulkner" <thefau@ju...

Macro not running on Office 2000, only 2003
Hi, Can anyone please tell me why the code below would run perfectly in Excel 2003, but not Excel 2000 (and 97 I assume). It stops on the Paste Special section: If Not IsDate([C1].Value) Then MsgBox "Please enter a date in C1!" [C1].Value = "Enter date here" Exit Sub Else thisDate = Range("Production!C1").Value Sheets("Data").Select ActiveSheet.Unprotect Range("G1:BH1").Select Selection.Copy Range("E6").Select Range(Selection, Selection.End(xlDown)).Select Selection.Find(What:=thisDate, Af...

corrupted record in table, won't delete or edit
I am dealing with an error that has ocurred in the past but has me baffled as to why it occurs. With no warning one random record in a table will appear to convert from "english" to "japanese?" The record will not delete, the record will not allow edits. The table will not then let me replace the bad record with a new one because it causes a "duplicate" in the SSN field. I have gotten around this in the past by copying the structure of the table and then copying and pasting the data into the new table leaving the bad record behind. However, in the past the er...

Can a VBA Function be used to execute (call) a Macro?
Does anyone know if a VBA Function can be used to execute (call) a Macro? How would you do this? A VBA function IS a macro. What exactly are you trying to do? On Tue, 23 Nov 2004 06:11:02 -0800, RKGriffin <RKGriffin@discussions.microsoft.com> wrote: >Does anyone know if a VBA Function can be used to execute (call) a Macro? How >would you do this? I am using the "Function," below, which appears to be different than a "Sub" Routine type Macro, because in a sub-routine I can call another sub-routine and in this thing, I can't. Actually, all I want thi...

Delete Team in MS CRM 1.2
Hi, just installing and configuring MS CRM 1.2 for my company. I am not familiar with this product. I want to do something very simple - delete a team I have created. I am logged in using an account with System Administrator rights. The instructions are very simple Delete a team 1.. Navigate to the Teams page. 1.. On the Home page, click the Settings side tab. 2.. On the Settings page, click Business Unit Settings and then click Teams. 2.. From the displayed list, select the team(s) that you want to delete. 3.. On the Actions menu, click Delete. 4.. On the Delete Confirm...

Macro
Hi, I don't know whether it is possible. Still I am asking the experts. My requirement is this. In a blank worksheet, if anything is typed in a cell, it must become like protected. Means it cannot be deleted or edited or retyped. This is required for the whole worksheet. Can anybody help? Thanks & Regards, Jaleel To lock all the cells in a Worksheet in EXCEL 2007 take the following action:- 1. Hit Ctrl-1 to launch Format Cells. 2. Protection tab. 3. Remove the green tick from the box called:- Locked - then hit OK. 4. Highlight all th...

Cannot delete messages #3
Hi, Using Exchange 2000 with Outlook XP - can't delete 2 messages with subject "Hey" and "Is that your attachment?". It seems like it was auto previewed but autopreview was off, however, the messages are marked as read. When you try to delete it, the popup says "The item could not be deleted. It was either moved or already deleted or access was denied." When you right click on the message(s), there are no options for it. Is there a way to delete messages like this globally on many mailboxes without taking the InfoStore down? ...

Spreadsheet print including macro buttons?
I am trying to print an Excel spreadsheet which includes macro buttons, but the button does not appear in print. Can anyone please help? Hi if you created the button from the Forms toolbar then it won't - if you create the button from either the control toolbox or a drawing object (both which can be linked to macros) then they will cheers JulieD "gunga" <gunga@discussions.microsoft.com> wrote in message news:62BE9933-FBCA-45E0-A9D4-C62EA751DD0A@microsoft.com... >I am trying to print an Excel spreadsheet which includes macro buttons, but > the button does not ...

Macro to delete rows that contain specific words
Hi, The macro i am trying to build needs to open a deisgnated file and each row in column A that has the given word to delete the row and move everything up. i.e. Mark apple apple Steve John apple So in the above example the data is in column A so the macro will scan through and delete the rows that display apple. Any help would be greatly appreciated. Thanks Neil Hi Neal There are a few ways on this example page http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Neil" <neil.pereira@nz.tele...

sharing macros
In an excel sheet, I assigned macros to custom buttons. Whenever I sent the sheet to someone else, the macros were still there but the buttons were not. So, once I send it to someone, do they have to create the buttons and assign it to the macros themselves, or is there someway I can save the worksheet or custom buttons so that when the sheet is opened on another computer they are already there? Do macros assigned to shortcut keys work the same way (i.e. they would only work on my computer)? Sorry if this has been asked before. I searched before I posted and couldn't find anything ...

macro / quicksaving emails
On my job I have very small HD space for my mailbox (appr. 175 mb). It is policy to archive all the mail in separate maps in explorer. And it is impossible for me to install programs on the computer. Therefore I have created shortcuts to projectmaps in explorer, and placed them in the Quicklaunch bar from Windows. I can than place a new mail with the mouse =F2n the shortcut, and it will be placed in the right map as a .msg file. It's the finding back that works badly. The filename in Explorer is the same as the subject text from the email. And the date noted in Explorer is ofcourse the d...

Help, I deleted some log files
Hey everyone, We are running Windows 2003 domain and Exchange 2003 in a cluster. Our drive where our log files lives was getting full for some unknow reason at the time so we deleted "some" of the oldest log files. Our server is up and running and everthing seems to be working except backups. We are using NetBackup 5.1. The backups are "partially successful". Okay so far. However, because it only "partially successful" it will not truncate the logs. There is some error about the logs: 9/7/2006 4:49:26 PM - Error bpbrm(pid=9428) from client wtvs1: ERR...

Can't delete items in Outlook
Every time I try to delete some contacts (and now mail messages) Outlook returns the message: "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." Needless to say, I have re-started Outlook ad nauseum. Anyone got a clue? Thanks, Cheryl Are you using a .PST file? If so, how big is it? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:f63c01c3bdcb$3abcd350$a601280a@phx.gbl, anonymous@discussions.micros...

macro disabled problem
I have a friend who has just upgraded from a previous version of Office to Office XP. He now has the following problem: "I have a problem with trying to open my accounts in excel. I open them using a Macro Page. The programme is telling me it will not open due to a high security risk and my macro has been disabled. Help!!!!" Can someone tell me a way to get around this please so I can advise him. Jon Sometimes upgrades change the macro security level. Tools|Macro|Security|Security level tab Choose medium to be promted each time choose low to never be prompted. Jonathan He...

1st Excel Macro -- Reference to a specific cell not wanted
I have recorded an excel Macro. I am trying to paste a function down for as many rows that are in my file. However, as shown in the following code, it is making reference to cell C2926, which happens to be the last row in the file that I used to record the macro. It causes my macro to crash if there are a different amount of rows in my file. Selection.End(xlDown).Select Range("C2926").Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Does anyone know what the proper code should be, or what sequence of commands I should do when recording the ma...

DoCmd to delete records
Hi I have a command button that when clicked deletes certain records from a subform based on an application number being equal. It deletes all records with the same applciation number as currently shown on the main form, see code below: DoCmd.RunSQL ("DELETE FROM tblAdultDetails WHERE [frmAdultDetails2].Form![ApplicationNum]=" + Str(Me.ApplicationNumber)) However I would like it to only delete records if the fields [MainApplicantFlag] =1 or [PartnerFlag] =1 as well as ApplicationNum being equal. These are both number fields btw. Can someone tell me what I should add to my code in...

Protect and Unprotect all worksheets with macro
I would like to protect and also unprotect all worksheets in the same workbook with a macro. I do not need to password protect as this is only for my own use. Also I would like to be able to protect / unprotect worksheets based upon the value of a cell within each worksheet. Thanks, Joe M. Sub ProtectAllSheets() Application.ScreenUpdating = False Dim N As Single For N = 1 To Sheets.Count Sheets(N).Protect 'Password:="justme" Next N Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpda...

Deleted Investments on Reports
Why do investments that I have long ago sold off still continue to appear on monthly or custom reports, or on the default home page summary statistics? Is there a way to configure Money to not report on investments I no longer have? I know they can be deleted from my portfolio, but then they won't appear on historical performance reports. But if I sold something years ago it's current month's performance shouldn't appear on my Monthly report. Am I missing something? Thanks. ...

Hiding and unhiding rows with if statement macro
Hello, I have a problem that I am unable to solve. I wondered if there is anyone who could think of a solution. I have two comboboxes (ComboBox1 and ComboBox2). In the first combobox the user can select whether they want to view rows 37-127 or rows 128-207. The code currently looks like this: Sub ComboBox1_Change() ' Combobox Value Is changed varData = Range("P21").Value2 ' unhide ranges On Error GoTo 100 Application.ScreenUpdating = False Sheet3.Range("A37:A127").EntireRow.Hidden = False Sheet3.Range("A1...

relative reference macro
I am trying to record a macro that will be relative to the cell that is selected. When I go to Excel help it tells me that on the "Stop Recording" toolbar I should click on the relative reference icon. however, I do not see any Stop Recording toolbar when I start recording a macro, nor do I see it in my choices of toolbars. Can someone tell me where I can find this icon? Thanks Jeff Donahue When you start recording a macro a toolbar should pop up which allows to stop recording. If you cannot see this when you record new macro (Tools/Macro/record new macro). Then when y...

Deleting Assign Categories
With each email that comes in I am asked to assign a color category. I haven no need for this feature. I use folders and that is enough. How can I turn this off so I can close and email and move on. This especially annoying when deleting spam files I still get more than enough of. Maximum Respect, Christopher Thompson Outlook itself doesn't do that, so an Addin should be responsible for that. For instance, Category Manager offers that feature. There you can turn it off via Tools/Options. -- Best regards Michael Bauer - MVP Outlook Manage and share your categ...

Email from myself going into Deleted folder
Using Outlook Express 6.0 and Yahoo mail. Starting yesterday, email to myself goes into my Yahoo mail account, but when I download it, it shows up in my Deleted folder instead of my Inbox. Yes, only email from myself. No, I don't have any Rules at all. Any ideas? Thanks. -- Not an Expert Ed wrote: > Using Outlook Express 6.0 and Yahoo mail. > Starting yesterday, email to myself goes into my Yahoo mail account, but > when I download it, it shows up in my Deleted folder instead of my Inbox. > Yes, only email from myself. > No, I don't have any Rule...