Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi Liliana,
If you used the macro as Auto_Open you would have to identify the workshee that this was to be applied to and probably create problems for future mainenance. You can really speed it up if you turn off calculation during the macro -- and turn it back on afterwards. http://www.mvps.org/dmcritchie/excel/slowresp.htm But that still took a long time, so with some additional changes 1 instead fo "A" and the use of WITH Sub delete_rows() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For row_index = lastrow To 1 Step -1 With Cells(row_index, 1) If .HasFormula + (Trim(.Value) = "") Then .EntireRow.Delete End With Next Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Frank Kabel" wrote in message ... Hi this macro only processes the used range. So specifying a range won't help. If you want to autoexecute it one way would be to rename the macro to Auto_Open() -- Regards Frank Kabel Frankfurt, Germany Thank you very much, Frank. It works!!!! It takes few minutes to run so I am wondering if there's a way to specify a range (let's say 5000 rows), maybe it will work faster. Also, is there an easy way for the macro to autoexecute when opening the file? (it's a shared file so it may be opened by other user) Thank you, Liliana --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Complex sheets - how can I delete rows without destroying formulas? | Links and Linking in Excel | |||
Delete a row without dirupting the formulas in the following rows | Excel Worksheet Functions | |||
Code to delete rows and column cells that have formulas in. | Excel Worksheet Functions | |||
How to delete rows using formulas | Excel Worksheet Functions | |||
with formulas that show negative results I want to show zero inste | Excel Discussion (Misc queries) |