| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 81447 | 2007-07-28 04:35:00 | Excel to delete rows with no data or zeros | lightfoot (39) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 573430 | 2007-07-28 04:35:00 | Does anyone know how to get excel to auto delete rows or columns where there are no data or just zeros. Much appreciate. | lightfoot (39) | ||
| 573431 | 2007-07-28 19:05:00 | Try here (exceltips.vitalnews.com) | johcar (6283) | ||
| 573432 | 2007-07-29 04:11:00 | 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? |
lightfoot (39) | ||
| 573433 | 2007-07-29 06:09:00 | 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 | dugimodo (138) | ||
| 573434 | 2007-11-16 07:28:00 | 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! |
gigistrom (12605) | ||
| 573435 | 2007-11-17 01:39:00 | Thanks bro, I am familiar with filter and sort functions. I'd like to learn a bit about macro. | lightfoot (39) | ||
| 573436 | 2007-11-17 19:46:00 | 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. |
beama (111) | ||
| 573437 | 2007-11-18 07:22:00 | 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: www.fontstuff.com Andrew |
andrew93 (249) | ||
| 1 | |||||