how to delete rows that show no value but contain formulas
 

Hello,

I am trying to find a macro that will delete rows that show no value o
text but have formulas in them. Can anybody help?

Thank you,
Lilian

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

how to delete rows that show no value but contain formulas
 

Hi
try the following macro:
Sub delete_rows()
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 - 1 To 1 Step -1
If Cells(row_index, "A").Value= "" and _
Cells(row_index, "A").hasformula then
Rows(row_index).delete
End If
Next
Application.ScreenUpdating = True
End Sub


-----Original Message-----
Hello,

I am trying to find a macro that will delete rows that
show no value or
text but have formulas in them. Can anybody help?

Thank you,
Liliana


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

.

how to delete rows that show no value but contain formulas
 

Thank you very much, Frank. It works!!!!

It takes few minutes to run so I am wondering if there's a way t
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 openin
the file? (it's a shared file so it may be opened by other user)

Thank you,
Lilian

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

how to delete rows that show no value but contain formulas
 

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/

how to delete rows that show no value but contain formulas
 

Thanks again. Works perfectly.

Lilian

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

how to delete rows that show no value but contain formulas
 

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/