Help revising slow code

I am using the following code to remove all the lines in a worksheet that 
have a zero value in Column H. Because there are thousands of rows in the 
worksheet, this loop takes forever! Does anyone have any suggestions for 
doing this more quickly? Thanks!

Sub Delete_Row_w0()
    Dim Firstrow As Long
    Dim LastRow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    
    With Sheets(1)
        .Select
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
        .DisplayPageBreaks = False
        Firstrow = .UsedRange.Cells(1).Row
        LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        For Lrow = LastRow To Firstrow Step -1

            With .Cells(Lrow, "H")

                If Not IsError(.Value) Then

                    If .Value = 0 Then .EntireRow.Delete

                End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .Calculation = CalcMode
    End With
    
End Sub
0
Utf
5/17/2010 2:39:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
785 Views

Similar Articles

[PageSpeed] 23

The following suggested macros assume Column H contains constant values and 
not formulas that display values. With that said, IF the cells in Column H 
will NEVER be blank when there is data in other cells on that row, then you 
can use this macro...

Sub RemoveZeroesFromColumnHAsLongAsThereAreNoBlanksInColumnH()
  With Columns("H")
    .Replace "0", "", xlWhole
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  End With
End Sub

If, however, there could be blanks in Column H on rows that have data 
elsewhere, then try this macro instead...

Sub RemoveZeroesFromColumnH()
  Dim LastRow As Long
  LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  With Range("H1:H" & LastRow)
    .Replace "", Chr(255), xlWhole
    .Replace "0", "", xlWhole
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    .Replace Chr(255), "", xlWhole
  End With
End Sub

As with all macros you are trying out for the first time, it is advisable to 
do your test on a copy of your worksheet and not on the actual worksheet 
containing your data... changes made by a macro cannot be undone.

-- 
Rick (MVP - Excel)



"Dani" <Dani@discussions.microsoft.com> wrote in message 
news:B66F9B6C-977B-403A-AB1E-771D5B463150@microsoft.com...
> I am using the following code to remove all the lines in a worksheet that
> have a zero value in Column H. Because there are thousands of rows in the
> worksheet, this loop takes forever! Does anyone have any suggestions for
> doing this more quickly? Thanks!
>
> Sub Delete_Row_w0()
>    Dim Firstrow As Long
>    Dim LastRow As Long
>    Dim Lrow As Long
>    Dim CalcMode As Long
>    Dim ViewMode As Long
>
>    With Application
>        CalcMode = .Calculation
>        .Calculation = xlCalculationManual
>        .ScreenUpdating = False
>    End With
>
>    With Sheets(1)
>        .Select
>        ViewMode = ActiveWindow.View
>        ActiveWindow.View = xlNormalView
>        .DisplayPageBreaks = False
>        Firstrow = .UsedRange.Cells(1).Row
>        LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
>        For Lrow = LastRow To Firstrow Step -1
>
>            With .Cells(Lrow, "H")
>
>                If Not IsError(.Value) Then
>
>                    If .Value = 0 Then .EntireRow.Delete
>
>                End If
>
>            End With
>
>        Next Lrow
>
>    End With
>
>    ActiveWindow.View = ViewMode
>    With Application
>        .Calculation = CalcMode
>    End With
>
> End Sub 

0
Rick
5/17/2010 3:25:19 PM
One thing I forgot... we need to protect the code against there being no 
zeroes in Column H. Put the following statement in as the FIRST line of code 
in whichever macro of mine that you use...

On Error Resume Next

-- 
Rick (MVP - Excel)



"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:e0CorUd9KHA.4308@TK2MSFTNGP04.phx.gbl...
> The following suggested macros assume Column H contains constant values 
> and not formulas that display values. With that said, IF the cells in 
> Column H will NEVER be blank when there is data in other cells on that 
> row, then you can use this macro...
>
> Sub RemoveZeroesFromColumnHAsLongAsThereAreNoBlanksInColumnH()
>  With Columns("H")
>    .Replace "0", "", xlWhole
>    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
>  End With
> End Sub
>
> If, however, there could be blanks in Column H on rows that have data 
> elsewhere, then try this macro instead...
>
> Sub RemoveZeroesFromColumnH()
>  Dim LastRow As Long
>  LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
>            SearchDirection:=xlPrevious, LookIn:=xlValues).Row
>  With Range("H1:H" & LastRow)
>    .Replace "", Chr(255), xlWhole
>    .Replace "0", "", xlWhole
>    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
>    .Replace Chr(255), "", xlWhole
>  End With
> End Sub
>
> As with all macros you are trying out for the first time, it is advisable 
> to do your test on a copy of your worksheet and not on the actual 
> worksheet containing your data... changes made by a macro cannot be 
> undone.
>
> -- 
> Rick (MVP - Excel)
>
>
>
> "Dani" <Dani@discussions.microsoft.com> wrote in message 
> news:B66F9B6C-977B-403A-AB1E-771D5B463150@microsoft.com...
>> I am using the following code to remove all the lines in a worksheet that
>> have a zero value in Column H. Because there are thousands of rows in the
>> worksheet, this loop takes forever! Does anyone have any suggestions for
>> doing this more quickly? Thanks!
>>
>> Sub Delete_Row_w0()
>>    Dim Firstrow As Long
>>    Dim LastRow As Long
>>    Dim Lrow As Long
>>    Dim CalcMode As Long
>>    Dim ViewMode As Long
>>
>>    With Application
>>        CalcMode = .Calculation
>>        .Calculation = xlCalculationManual
>>        .ScreenUpdating = False
>>    End With
>>
>>    With Sheets(1)
>>        .Select
>>        ViewMode = ActiveWindow.View
>>        ActiveWindow.View = xlNormalView
>>        .DisplayPageBreaks = False
>>        Firstrow = .UsedRange.Cells(1).Row
>>        LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
>>        For Lrow = LastRow To Firstrow Step -1
>>
>>            With .Cells(Lrow, "H")
>>
>>                If Not IsError(.Value) Then
>>
>>                    If .Value = 0 Then .EntireRow.Delete
>>
>>                End If
>>
>>            End With
>>
>>        Next Lrow
>>
>>    End With
>>
>>    ActiveWindow.View = ViewMode
>>    With Application
>>        .Calculation = CalcMode
>>    End With
>>
>> End Sub
> 
0
Rick
5/17/2010 3:40:56 PM
Reply:

Similar Artilces:

Code to fill Word form for multiple records from Access
This is the code I've been working with, but it freezes Access: Private Sub Command66_Click() 'Print Physician Profile. Dim appWord As Word.Application Dim doc As Word.Document 'Avoid error 429, when Word isn't open. On Error Resume Next Err.Clear 'Set appWord object variable to running instance of Word. Set appWord = GetObject(, "Word.Application") If Err.Number <> 0 Then 'If Word isn't open, create a new instance of Word. Set appWord = New Word.Application End If Set rs = Db.OpenRecordset rst.Open Me.RecordSource, CurrentProject.Co...

Help building Group Expense Sheet with Equal Allocation
undefinedundefined I am trying to build a Spreadsheet that will allow me to distribut costs equally to all members of the group for vacation purposes. Fo instance, if there are 10 members, and one group member pays $100, an another pays $50, and the rest of the group does not contribute. Tha means that each member is required to pay $15, but b/c members 1 & already paid, they are entitled to reimbursement. What I want to b able to calculate is how much each person in the group owes the othe people. Obviously person one is entitled to $85 and member 2 i entitled to $35, but I want to ...

Please Help...can't recover e-mail
Hi, I helped my mom restore her computer, and backed all of her outlook files to a CD. I tried putting the .dbx and folder info files back into outlook, and it won't recognize them at all. I tried converting them to excel, access, and just about everything under the sun, and still no look. Does anyone have any ideas? Thanks Hi - if you have dbx files, they're for Outlook Express, not Outlook. You'll need to copy them to the hard drive to the folder of your choice and remove the read-only attribute they inherited from the CD. Then you can direct Outlook Express to use that f...

Help! Why can't I log on????
I am receiving my email on my Blackberry but, not on my home computer. Ever since last night I keep getting this message: Account: 'pop.charter.net', Server: 'pop.charter.net', Protocol: POP3, Server Response: '-ERR invalid user name or password.', Port: 110, Secure(SSL): No, Server Error: 0x800CCC90, Error Number: 0x800CCC92 Any help would be greatly appreciated. Thank You! First, test your password by logging in to your provider's webmail: http://www.charter.net/files/charter/redirect.php?to=webmail If that works, it proves you have the correct...

Need Help Please
How can I CREATE a powerpoint 2003 background (color scheme)? Can you be more specific about what you're trying to do? A background can be based on a color scheme, but it doesn't have to be. So I'm not exactly sure what you're really asking. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2007? http://www.echosvoice.com/2007.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/32a7nx "kpickren" <kpickren@discussions.microsoft.com> wrote in message news:6F8849...

Help with attachment blocking (odd problem)
Hello, We are having problems with *something* blocking our emails that contain html attachments. This is not a problem internally. This only occurs when attempting to send them out of our network. Occasionally these messages will go trhough but duplicate on the receivers end. The messages continually retry (on our relay server) and eventually bounce back after the 2 day timeout. I have tested multiple emails with differen't attachments, no attachments, etc -- the only attachment with this issue is in html format. I cannot find where these attachments are being blocked. We have two MS I...

Outlook 2003 -Windows XP- very slow
I have more than 100.000 addresses,it takes about 5000 adrs per hour. After import it is VERY VERY slow,have to wait 15 -25 seconds to get choosen address. I defragm both Hdisk and Database feature in OL2003. Why ?? William Rippen Holland 100,000 addresses!!??!! WOW! You can't know that many people, so I guess you manage a client database (or you're a spammer). In any event, first empty your deleted items folder. Then archive old messages to split your .pst file into something smaller. That should help. The truth is, while I'd normally think that would be sufficient, with...

Help #2
Hi, I have a problem. I have made a c# application, which I call from crm by pushing a button. This app uses the sharpziplb library to make zip files (.net framework 1.1, no zip class included). I call the app using xmlhttp - activexobject. When using an admin user, no problem it works fine. But I use another user, the problems begin. First I was prompted for login and pwd 3 times followed by the 401.1 error. This was solved by setting the ie security to low. Then I received this strange error stating that i didn't have the required credentials to use the sharplibzip dll which is ...

Access Relationship Help
Hi, I am having trouble working with Access. Here is my scenerio: I am suppose to create an Entity Relationship Diagram for a specific problem. The categories for creating an ERD are: Song Title, Artist, Album, Genre, Duration, Location, and Tempo I came up with a relation: Artist -> Album -> Song -> Duration, Location, Tempo Artist -> Genre I tried to create this relationship with the Table Wizard; however, I do not know exactly how to create this. How many tables do I need? Do I need to all 7 tables, or do I need only 3 main tables (and put the others in subcategories)? ...

Support Code:85010001
Howdy, I just purchase a Sprint PPC-6700 in the hopes that it would sync directly to my Exchange SP2 server. The error message that I receive is "Your account in Microsoft Exchange Server does not have permission to synchronize with your current settings. Contact your Exchange Server administrator. Support Code:850100001." Does anyone have a resolution to this problem? Thanks, Prayer Sorry, no help, but I'd love to hear what you think of the 6700 once you get it working! "Solanky, Prayer S." wrote: > Howdy, > > I just purchase a Sprint PPC-6700 i...

i created folders and now they are gone help!
I'm on outlook 2007, put important emails in there and poof, gone! it's magic Check your View. "jdoubleh" <jdoubleh@discussions.microsoft.com> wrote in message news:3EEAA52B-897C-437D-AA01-15983153EBAC@microsoft.com... > I'm on outlook 2007, put important emails in there and poof, gone! ...

Tracking Sheet help.
Sheet 3 of my workbook is a tracking sheet. In cell B8 (sheet 3) I enter the amount of time's that I've made an entry of time on sheet 2. The times I enter on sheet 2 are used to track vehicles that are passing a set location. On sheet 2 I enter the times in column D. The vehicle's I count have numbers to ID them in column F. The numbers look like 401N1501, 401N1514, 401S1516, 401S1600. The part of the number I need to associate with my count is the first four digits, 401N, 401S. Is it possible to do this? Yep, use a sumproduct function to count using the left 4 digits as...

Need help to re-install software Windows Professional
I recently re-installed the above program after having to send my computer away. I am now recieving the following message " any active activation context" when I try to download anything, such as windows player. When on the internet, I get my home page, Yahoo, but as soon as i try to enter a web address it prompts a message "Microsoft feeds synconisation". Could anyone please offer any advice as to how to rectify the problem. And this is about Windows XP Professional x64? chrissiebell1804 wrote: > I recently re-installed the above program after havin...

keybd_event and labels HELP!!!
Hi, I'm writing an "on screen keyboard" like the windows one, with Visual C++ 6 and MFC support. I'm looking at SendInput and keybd_event functions to simulate keyboard events. I saw there are many problems (maybe focus derived) to send character messages to other windows like Internet Explorer... and in general with all the applications in which the cursor is active in a text box. Is there a solution to this problem? Thanks a lot! ;) ...

Looking for help with database functions
I am working with a non-profit rural community development group. We are trying to put together a "database" to put people looking for work together with people needing services. A sample would might be: Joe Blow; [carpenter, woodworker]; free on tues & thur; willing to trade (equal to $10.00 hr); References: Heidi Hoe, 555-5555, John Doh, 555-5555 ext 100 Jane Doe, needing porch repaired and general yardwork, work on your own schedule, open to Barter; References: Seymour Johnson, 555-5555, Ivan Tootall, 555-5555 Winnie Pooh, looking for someone to read news, books, a...

Formula help please #4
I want to countif column C is 2-Aug-04 & Column D is tp015& Column E is 23. I need to capture this all in the same function in order to shorten time for data entry, and it allows me to get the speciic data needed ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** Hi try =SUMPRODUCT(--(C1:C100=DATE(2004,8,2)),--(D1:D100="tp015"),--(E1:E100=2 3)) For more about Sumproduct see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany &quo...

Retrieve Product Key Codes
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel We mistakenly left our disk sleeve on a wet counter and ripped the product code stickers. We can only read the last ten digits of each of the 3 product key codes. How can we retrieve replacement key code numbers? <br><br>Thank you, Sue On 2010-02-28 14:16:48 -0800, Sue_Burgess@officeformac.com said: > We mistakenly left our disk sleeve on a wet counter and ripped the > product code stickers. We can only read the last ten digits of each of > the 3 product key codes. How can we retr...

Cannot send messages
Hi all, thanks in advance for reading this. Our router died and we got a new one. I had the port 25 and 110 mapped, which allowed all the incoming to come in, but the outgoing is still qued and will not send. Any ideas? Exch 5.5. THANK YOU. Question 1 Do you have a Firewall ? What Flavor ? Question 2 What version of Exchange are you running ? Question 3 Did you do the following in MS Knowledge Base article. 153119 "David" <anonymous@discussions.microsoft.com> wrote in message news:1222c01c3c011$df8ad2e0$a601280a@phx.gbl... > Hi all, thanks in advance for reading...

Formula / Macro Help
Hi, Can anyone help me? I have some text in cell A1 and i wish it to search in column A on other worksheets for that reference. If it then finds a match on one o the sheets i want it to tell me on sheet1 in B1 that its in worksheet 4 for example ie sheet1 A1 = Toy Car sheet4 A335 = Toy Car sheet1 B1 = Sheet4 Can this be done?? Any help appreciated Regards Crai -- BSLAUTOMATIO ----------------------------------------------------------------------- BSLAUTOMATION's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=761 View this thread: http://www.excelfo...

How do I code the cloning of an object in vba?
Specifically a frame containing both a textbox control and a spinbutton control. Thanks Steve Can you be more specific? What are you wanting to do? Are you just wanting to copy a control to another userform? -- Cheers, Ryan "sarndt" wrote: > Specifically a frame containing both a textbox control and a spinbutton > control. > > Thanks > > Steve ...

need some help
Hi, This is probably a stupid question, I have been trying to understand but I would need some to explain it. I have an SDI app (a treeview to the left and a CFrameWnd to the right, I switch between a couple for CFormView) I don't have a CDocument class in my project, so I get these kind of errors "Warning: Creating a pane with no CDocument." Now my problem is I don't know why I need a CDocument. In the msdn help I can read "CDocument supports standard operations such as creating a document, loading it, and saving it. The framework manipulates documents using the interfa...

supplier info confusion HELP!!!! :o)
Can somebody explain this .... I have an item on my DB which is supplied by 3 different suppleirs, but one ILC. If I run a sales report and group it by supplier, will it know how many i have sold from each supplier? And if so how does it know?! Thanks! I think the sale gets credited to the primary supplier on the detailed sales report...tried it on my laptop where I did the po and received the po for a secondary supplier assigned to the item, the detailed sales report credited the sale to the primary supplier....I looked at the transactionentry table and there is no reference to th...

Suggestions / Help
Hi: I would like some advice on setting up the budget and accounts in Money 2003. I have been using excel for years to do our finances but would like to switch to Money 2003. I believe the problems I am having are not unique. I am open to suggestions for improving the workflow. Any help would be appreciated. Here is some background information: * A primary checking account. * A primary savings account * An ING account * Two credit cards * Mortgage * CarLoan * One 401(k) * Two Roth-IRAs I have a fairly detailed budget. All of the monthly bills are on autopay...

CPropertySheet ! Pls. Help
I have made a propertysheet like this : m_pTabCtrl = new CPropertySheet(""); // PAGES m_pClientInfo = new CRoomClientChkin(); m_pPayInfo = new CRoomPayInfo(); m_pTabCtrl->AddPage(m_pClientInfo); m_pTabCtrl->AddPage(m_pPayInfo); m_pTabCtrl->Create(this, WS_CHILD | WS_VISIBLE); The problem is for example if on the RoomClientChkin dialog I put a button and on that button onClick I wand to show a dialog with DoModal() my program faild. For ex : void CRoomClientChkin::OnAddClient() { // SHOW A DIALOG AfxMessageBox("test"); // the program wil faild. ...

help with hyperlinks
I am creating multiple worksheets and often must cut and paste, though I find that often hyperlinks will be in cells that have never been used before. They do not even appear as text, but when you scroll over them the cursor changes. why does this happen? is it from cutting and pasting? As well, my hyperlinks are all down the same column yet often do not correspond to what they orginally were supposed to be, but rather link to the site of the company 2 rows down. anyone have any ideas? thanks ...