PDA

View Full Version : Excel to delete rows with no data or zeros



lightfoot
28-07-2007, 04:35 PM
Does anyone know how to get excel to auto delete rows or columns where there are no data or just zeros. Much appreciate.

johcar
29-07-2007, 07:05 AM
Try here (http://exceltips.vitalnews.com/Pages/T1022_Conditionally_Deleting_Rows.html)

lightfoot
29-07-2007, 04:11 PM
Thanks bro. Looks like got to learn some macro.

Where is the love that wisdom, knowledge and information bring - for what really is the purpose?

dugimodo
29-07-2007, 06:09 PM
I don't know how to do it automatically but you can turn on the autofilter, filter for blanks, highlight first row or column, press ctrl - shift - down or right etc to highlight whole block and delete all at once fairly quickly

gigistrom
16-11-2007, 08:28 PM
I recently had to do something similar -- here's what worked for me:

first I put in the value I wanted to filter by to find the rows to delete. In your case, you can skip the first 2 rows and put in 0 for vCriteria
Sheets("lookup").Select
Range("M3").Activate
vCriteria = ActiveCell

Next I went to the sheet I wanted to filter, cancelled any previous filters, and refiltered according to the new criterion.
Sheets("inflow").Select
ActiveSheet.AutoFilterMode = False
Range("A1:J1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:=vCriteria

Finally, I deleted the visible rows -- that is, all the ones with the value I chose.
Range("A2:J2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).EntireRo w.Delete
ActiveSheet.AutoFilterMode = False


Hope it works for you -- good luck!

lightfoot
17-11-2007, 02:39 PM
Thanks bro, I am familiar with filter and sort functions. I'd like to learn a bit about macro.

beama
18-11-2007, 08:46 AM
if you want it done on opening the spreadsheet create the Macro and call it "autoexec"

That Marco will then run first thing everytime you open the spreadsheet.

andrew93
18-11-2007, 08:22 PM
There are a lot of free tutorials and tips on the internet. You have already been provided with a pretty good link for your topic. The following link might help with understanding VBA (the macro language) a little better:
http://www.fontstuff.com/vba/vbatut01.htm

Andrew