rows delete via macro needs too much time

Hello all,
I wrote a macro which have to delete some rows in a sheet. 
The sheet contains many sum formulas. When I start the 
macro, it work very well but after 10 deletes it needs 
more time than before and after 20 deletes it needs over 
30 seconds for one delete. When I break the macro and do 
the delete manually, the same happen.
0
anonymous (74722)
6/25/2004 8:32:05 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
696 Views

Similar Articles

[PageSpeed] 47

Post the code.  Also, have you tried turning off screenupdating and calculation
whilst it is running, and then putting it back on at the end:-

Sub xyz()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

    Code................

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub



Various examples of code to delete rows:-

Sub DlBlnks()

On Error Resume Next     ' In case there are no blanks
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange 'Resets UsedRange for Excel 97

'more information in
'Delete Cells/Rows in Range, based on empty cells, or cells with specific values
'http://www.mvps.org/dmcritchie/excel/delempty.htm
End Sub

-----------------------------------------------------

Public Sub DeleteReallyBlankRows()
'Chip Pearson
'Will delete all rows that are entirely blank
Dim r As Long
Dim c As Range
Dim n As Long
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
    Set Rng = Selection
Else
    Set Rng = ActiveSheet.UsedRange.Rows
End If
n = 0
For r = Rng.Rows.Count To 1 Step -1
    If Application.WorksheetFunction.CountA(Rng.Rows(r).EntireRow) = 0 Then
        Rng.Rows(r).EntireRow.Delete
        n = n + 1
    End If
Next r
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

-----------------------------------------------------

Sub DeleteEmptyRows()
'John Walkenbach
'Will delete all rows that are entirely blank
    LastRow = ActiveSheet.UsedRange.Row - 1 + _
        ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = False
    For r = LastRow To 1 Step -1
        If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
    Next r
End Sub

-----------------------------------------------------

Sub DeleteEmptyRows2()
'John Walkenbach Edited
'Will delete all rows where E:AI is entirely blank
    LastRow = ActiveSheet.UsedRange.Row - 1 + _
        ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = False
    For r = LastRow To 1 Step -1
        If Application.CountA(Cells(r, 5).Resize(1, 31)) = 0 Then Rows(r).Delete
    Next r
End Sub

-----------------------------------------------------

Public Sub DeleteBlankRows():
'This will delete all the blank rows if cell in Col A is blank within the active
sheet.

        On Error Resume Next
        Intersect(ActiveSheet.UsedRange.EntireRow, Columns(1)).SpecialCells( _
                xlCellTypeBlanks).EntireRow.Delete
        On Error GoTo 0
End Sub
Public Sub DeleteSelectionBlanks():
'This will delete all the blank rows contained within a selection of blank rows.
'Select by dragging down on the row handles to select entire range containing
rows
'you wish to delete.

        On Error Resume Next
        Intersect(Selection.EntireRow, Columns(1)).SpecialCells( _
                xlCellTypeBlanks).EntireRow.Delete
        On Error GoTo 0
End Sub

-----------------------------------------------------

Sub DelRows1()

ans = InputBox("What string do you want rows to be deleted if they contain it?")
Application.ScreenUpdating = False

LastRow = ActiveSheet.UsedRange.Row - 1 + _
        ActiveSheet.UsedRange.Rows.Count

Set Rng = Range(Cells(1, "A"), Cells(LastRow, "A"))

With Rng
   .AutoFilter
   .AutoFilter Field:=1, Criteria1:=ans
   .SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Application.ScreenUpdating = True

End Sub

-----------------------------------------------------

Sub Delete_Rows()

Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
        If InStr(UCase(Cells(RowNdx, "A").Value), "OLD") Then
            Rows(RowNdx).Delete
        End If
Next RowNdx

-----------------------------------------------------

End Sub
Sub DelBlankLookingCells1()
'Note:- Cells not rows
Dim Rng As Range
Dim cel As Range
Dim DelRng As Range
Set DelRng = Nothing
Set Rng = ActiveSheet.UsedRange

For Each cel In Rng
  If Len(Trim(cel.Value)) = 0 Then
      If DelRng Is Nothing Then
         Set DelRng = cel
      Else
         Set DelRng = Union(DelRng, cel)
      End If
  End If
Next
If Not DelRng Is Nothing Then
   DelRng.Delete Shift:=xlToLeft
End If
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 :-)
----------------------------------------------------------------------------



"PZ" <anonymous@discussions.microsoft.com> wrote in message
news:2144b01c45a8e$e556bfa0$a101280a@phx.gbl...
> Hello all,
> I wrote a macro which have to delete some rows in a sheet.
> The sheet contains many sum formulas. When I start the
> macro, it work very well but after 10 deletes it needs
> more time than before and after 20 deletes it needs over
> 30 seconds for one delete. When I break the macro and do
> the delete manually, the same happen.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004


0
ken.wright (2489)
6/25/2004 9:29:47 AM
Also note that none of these do any selecting, which if you have recorded and
edited code, you may well find that yours do.  This will slow done any routine
significantly, and should be avoided if possible.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------


Snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004


0
ken.wright (2489)
6/25/2004 9:42:11 AM
Reply:

Similar Artilces:

Excel VBA
Hi VBAers, I need to copy autofilter rows from one worksheet to another (within the same workbook). My autofilter code is: Columns("W:W").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="TRUE" I'm not sure how to do the following: 1) code to copy the rows found to the other worksheet 2) how to cater for the situation if no rows are found. I've researched but all the help Google returns is rather confusing. Can someone please advise. On Feb 16, 2:43=A0pm, kazzy <kazm...@gmail.com> wrote: > Hi VBAers, > > I ne...

Time Stamp
Is there a way to date and time an entry without using the "NOW" an "TODAY" function (as it gets updated everytime the worksheet i opened)? For instance, how is it done so that everytime a letter is entered in column, the column next to it will populate the date/time the letter i entered in the spread sheet? Is this do-able? Thanks for any info -- Message posted from http://www.ExcelForum.com Hi have a look at http://www.mcgimpsey.com/excel/timestamp.html -- Regards Frank Kabel Frankfurt, Germany > Is there a way to date and time an entry without using the &quo...

XSLT: Loading Single Image Multiple Times
I've built and XSLT that is used to generate a simple TreeView-type, web-based control (ASP.NET / C#). When the tree is fully transformed (client-side, JavaScript transformation), there are about 5 different images that are referenced (page.gif, folder.gif, plus.gif, minus.gif, and clearpixel.gif). What I assumed would happen would be that once it loads page.gif, every place in the transformed HTML document that references page.gif would load at the same time. However, what really happens is the client will request the image page.gif for each and every instance of page.gif on th...

Outlook Task Reminder times in CRM 4
Working with the Outlook client in CRM 3 I could set a specific date and time of day for a reminder in the Outlook task. With CRM 4, no matter what time of day I set the reminder for in Outlook it is overwritten by CRM on the next synch to 12:00 am. Is there any way to change this behavior? ...

Deleted Items in Inbox
How can I get Outlook to stop showing deleted items in the Inbox? What version of Outlook are you doing? Are you using IMAP and if so have you purged your deleted items yet? -- -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com "Jeffery Rehm" <jr@jrehmconsulting.net> wrote in message news:#nW#$NAoIHA.3976@TK2MSFTNGP03.phx.gbl... > How can I get Outlook to stop showing deleted items in the Inbox? > IMAP? Use a view that hides deleted items and purge the folder occasionally -- Diane Poremsky [MVP - Outlook] Author, Teach Your...

moving client to marketing list via workflow
is this possible, i cant seem to find this? "Jonny Nachman" <JonnyNachman@discussions.microsoft.com> wrote in message news:8BC3B472-8558-42E2-89EC-BDDCC083434B@microsoft.com... > is this possible, i cant seem to find this? Where I work we ended up writing a add-in to workflow to do it. -- Robert MacLean http://www.sadev.co.za ...

Need help with a function
Hi I'm sorry but I'm not too much familiar with Excel. I have some listing in column A For example in A1 I have: D:\blabla\folder1\file1.xxx in A2 I have: D:\blabla\folder2\file2.xxx in A3 I have: D:\blabla\folder3\file3.xxx I want the word between the 2nd and 3rd '\' to be in color red. So in A1 folder1 should be in red, in A2 folder2 in red etc I could do it manually but there are like 3000 lines. It would take forever. Is this simple to achieve with a script/macro/? ? I hope someone can help me. Thanks in advance. Christophe Presumably you want to pick that value o...

Autofill of row or column
Greetings, Let's say I have a min, a max, and an increment, all decimal values. I want to autofill or autopopulate a row or column -- without manually dragging cels -- over the range [min,max] incrementing by the increment value each cell. Can this be done somehow? Also, what if you don't know how many cells you will need initially (you return some variable integer from a calculation)--can you tell Excel to fill only that number of cells each time? For example, let's say I compute some number and get a 7 one time and a 10 the next, and I want 7 cells filled with data and then ...

how do u times hours by wages
im trying to create a roster on excel but i cant times the hours my staff worked by there wage cost? i have in b2 1:30AM b3 4:30PM then in b5 the total is 3:00 the formula i used was =b3-b2+(b3<b2). i want to times b5 by there wage cost $16.00 i dont know what else to do or if i have done that last formula wrong? please help Nikki =(b3-b2+(b3<b2))*b5 Nikki wrote: > im trying to create a roster on excel but i cant times the hours my staff > worked by there wage cost? > i have in b2 1:30AM > b3 4:30PM then in b5 the total is 3:00 the formula i used was =b3-b2+(b3<b2)....

Service time billing and security
Can MSCRM be adapted to a service organization that primarily bills for time on an as-provided basis? When resolving a case, I see that the billable time is shown as the total of all activity time. Is this information only used when applied against a contract, or can I invoice for time or export a list of billable time with descriptions for use in Quickbooks? Also, can information, such as usernames and passwords be stored in the account record but secured from users that don't have sufficient privileges? Thank you, Rick S. Sorry about the multiple postings. When posting, it said ...

How do long-time owners of Office 2004-Student-Teacher order 2008 version?
There have been lots of promotions for those who are not already registered for Office 2004-Student-and-Teacher to buy that version and then buy into Office 2008-Student and Teacher. That's a great deal for them. But how do those of us who are long-time users of 2004 place our orders for Office 2008 Student-Teacher? Respectfully, Norm On 1/7/08 6:08 PM, in article C3A81B05.94B%nnager@vnoxsxpxaxmv.fullerton.edu, "Norman R. Nager, Ph.D." <nnager@vnoxsxpxaxmv.fullerton.edu> wrote: > But how > do those of us who are long-time users of 2004 place our orders for Of...

Calculating Time/Days worked
Hope you will bare with me while I explain. Once again I am working with a sheet, or series of sheets that hel employees calculate their time for days and hours worked. I decided against the protection button, as it was more of a proble with employees unchecking it. So, I am left with one last functio before it is complete. Let me remind you wonderful people I am new to excel and this is m first real project. Ok, my last 'function' I would like is this... Paydays are calculated from the 11th of each month to the 25th, the again from the 26th of each month to 10th of the the next month...

Changing Slide Advnacing timing
Thanks for your attention. I have a PowerPoint presentation with more than 30 slides. Initially I set the slide advancing to AUTOMATICALLY AFTER 00:05 secs for each slide. Rest of the Slide Transition settings for each slide are different. Now I wish to reduce the timing to 00:03 secs without changing other setting. (clicking APPLY TO ALL SLIDES changes the Slide Transition settings in all slide to identical as the one I am working on). 1. Is there a way to do this easily without having to go to each slide individually? 2. If there is a way, can it be used to change ...

Rules / Macro? Help
This is a multi-part message in MIME format. ------=_NextPart_000_0042_01C478A8.C4D615E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello. This is what I would like to do. If the subject line of the e-mail I = just received reads " Cat " then I would like Outlook to automatically = send/forward the " Cat Template ". If the subject line of the e-mail I = just received reads " Dog " then I would like Outlook to automatically = send/foard the " Cat Template ". I've looked into trying to ...

print column labels with one row of data at a time.
I am a teacher. I have created my gradebook in excel. (I don't have access). I have 7 worksheets (one for each class that I teach). The worksheets are not exactly the same. I would like to be able to print out the top two rows (column labels) with each students data (row). This way the student can see row 1 (name of assignment), row 2 (points), their row (their personal data for each assignment). Any help?? Go to File > Page Setup > Sheet Then in the "Rows to repeat at top" highlight the label headings row(s). HTH -- _______________________ Naz, London...

Muting Macros
I have several macros which, upon execution, flash updates to the screen with blinding speed, displaying the results of the commands. How can I inhibit this window chatter once the macros are de-bugged? -- Gary's Student Hi Gary's, > How can I inhibit > this window chatter once the macros are de-bugged? > Application.ScreenUpdating=False 'Your code Application.ScreenUpdating=True Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com First, Application.ScreenUpdating = False '<Your code here> Application.ScreenUpdating = True Secon...

automatically numbering rows
I wish to have rows automatically numbered in column "A" according to the content in column "B". If column "B" has text or numbers in a cell I do not want to number that row in column "A". If column "B" is blank in a cell I want to number that row in column "A". I found the following formula that works the opposite of what I want "=IF(B1<>"",COUNTA($B$1:B1)&".","")" . What would the formula be if I wanted to numerically count the blank cells in "B" and skip the conte...

graph troubles in VBA macros
Hello, I'm having troubles with creating some graphs with an Excel VBA Macro. I would like to create about 35 graphs from a set of data stored in a unique datasheet. As the set of data can change (length, numbers, etc ..) I created a macro to generate the graph starting from a template graph. The macro runs correctly a few times but after a variable number of graph (from 5 to 15, it depends on the weather), it crashes with : Run-Time Error '-2147417848 (80010108)' : Methode 'XValues' of object 'series' failed If i go in debug mode and repeat the error, I get : ...

Use query for Row Source?
I attempted to use a combo-box to look-up records on a form. The RowSource query that is generated by the wizard included 2 data fields. I wanted to show only a distinct list of the second, non-key data element (col1). When I changed the RowSource query to SELECT distinct Col1, the combo-box displays nothing. I then created a stored query with the SELECT distinct Col1. They query runs correctly, but when I put in in the RowSource, it displays nothing. Any clue what I could be doing wrong? On Fri, 26 Oct 2007 12:17:03 -0700, JHC wrote: > I attempted to use a combo-box to look-u...

How to Link to the next blank row after the last entry in a column?
Cell BM6 Sheet1, has a formula that gives me a numeric total. The total belongs in Column D1 Sheet2 after the last entry, and there will be cells that will remain blank in that column before the last entry. Sheet1 is a work sheet, which will be saved with a new file name at the end of the month after the totals have been sent to Sheet2. Sheet2 is a permanent, ongoing record. The numbers on original Sheet1 will then be erased and the worksheet reused for the new month. :confused: How do I get the values into the appropriate row of Sheet2 and have them remain there when I reuse the workshee...

Get time from a remote server with PowerShell v2
Hi all, I'm looking for a way to obtain the time from a remote server. I wanted to use Get-Date but this doesnt seem to have any options for remote computers so I'm using WMI Win32_LocalTime option like this: $hour = Get-WmiObject Win32_LocalTime -computerName servername | Select-Object -Property Hour $minute = Get-WmiObject Win32_LocalTime -computerName servername | Select-Object -Property Minute $second = Get-WmiObject Wind32_LocalTime -computerName servername | Select-Object -Property Second However this returns values in the form of (for example): @{Hour=16}...

Macro needed to update a spreadsheet
Hi, Group Basically, I had a query and a worsheet. The query namely “Updated” has 8 columns, while the other worksheet “Live” has 16 columns (8 of them are same with “Update”, rest are different). However, both of them have a same indicate column “Equipment ID”. What I need is to use the “Updated” query to update the “Live” one. That is whenever, there’s a change in the “Equipment ID” of “Update” I need the changes .including all other 8 columns, to be reflected on the “Live” sheet, no matter it’s an addition, deletion or just change of value. Did I make myself clear? Thanks Not entirel...

_T macro across multiple lines!
Hi, We have the _T() macro which is supposed to be used for ansi/unicode compilations with string literals. But how do you use it with strings that span multiple lines? Ok: CString str = _T("a short string"); No ok: CString str = _T("A long string I might " "want to break up over a " "few lines"); Thanks "markww" <markww@gmail.com> wrote in message news:1155693172.252663.158120@m79g2000cwm.googlegroups.com... > Hi, > > We have the _T() macro which is sup...

Copy/Paste with Hidden Rows/Columns Excel 2007
I am working with rather large & detailed spreadsheets. I have hidden rows and columns with supporting details that feed into the viewable results (formulas). I need to take the “zipped up” data and copy/paste to a new worksheet. When I paste the data, it appears with all of the hidden columns and rows visible one again. Is there any way to paste the zipped up data without the reappearance of the previously hidden rows and columns? Can you paste a link without all of the hidden data reappearing? Select range to copy which includes hidden rows and columns. F5>Special...

including solver in a Macro
I am working on creating a macro that will identify cells to be used in the solver as the "target cell" and the "by changing cell" boxes. I also need it to pick up a figure in the "equal to value" box from a specific cell in the spreadsheet. Once these are defined, it needs to begin the solving process. When it is done, go on with the rest of the macro. I hope this makes sense. Thanks, Scot Scot, A good starting point is often to record a macro doing the solver with your parameters, and look at the resulting code. Earl Kiosterud mvpearl omitthisword at ...