Hi everyone, There has got to be a simple way to do this… After after sorting, filtering, editing etc. I have a sheet with lots blank rows mixed in with the data. Is there a function/ tool that automatically removes blank rows? many thanks in advanced!
Copy, edit, paste special, skip blanks
^ i dont think i have ever gottent he skip blanks thing to work edit: skip blanks skips blank cells for pasting so it doesnt copy over exsiting data, not the same thing that original psoter was asking about
I’ve had this problem before and just did a quick google search and copy and pasted a macro into vba. Shouldn’t take more than 3 mins. Edit: Sub DeleteBlankRows3() 'Deletes the entire row within the selection if _ the ENTIRE row contains no data. Dim Rw As Range If WorksheetFunction.CountA(Selection) = 0 Then MsgBox “No data found”, vbOKOnly, “OzGrid.com” Exit Sub End If With Application .Calculation = xlCalculationManual .ScreenUpdating = False Selection.SpecialCells(xlCellTypeBlanks).Select For Each Rw In Selection.Rows If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then Selection.EntireRow.Delete End If Next Rw .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub
that didn’t work : (
doworkson … I’m not great w/ VBA, but I’ll try that, THANKS!
You can use “Non-Blanks” with the Auto Filter and then copy and paste everything to a new spreadsheet. That could be a pain if you have a lot of columns, tho
the problem is I’d like to stay in the same sheet b/c I have a bunch of pivots running off of it. I tried the above filtering for blanks and deleting, but thats deletes everything (thank God for ctrl-Z) VBA script locked me down- the dreaded “not responding”…force quit time
Can’t you just select all of your data and sort by one of the fields? Then all of the blank rows should show up at the bottom of your data.
you can just sort the columns ascending/descending and then all of the blanks will be grouped together, making it easy to delete the blank rows…hopefully, deleting those rows will automatically also adjust the ranges that your pivots are using
wanderingcfa Wrote: ------------------------------------------------------- > Can’t you just select all of your data and sort by > one of the fields? Then all of the blank rows > should show up at the bottom of your data. great minds…
unfortunately I need to keep the original order
but good idea… I’ll insert a column and number rows to reference current order, sort by another column, and resort non blank rows by inserted order reference column and delete the column when done. thanks! I assumed there would be a simpler way- time to write msft
The other options is as follows: Go to edit, goto, special, select blanks. Then go to edit, delete, and select entire row. That should work.
google “ASAP Utilities” and download it; it’s the best add-in I’ve ever seen, basically they’ve taken all the simple things you *wish* excel could do and have created macros for it; there’s a specific function that lets you remove all empty rows with the click of a button (and no, I don’t shill for whoever created this add-in).
this looks like a great app… thx.
doworkson Wrote: ------------------------------------------------------- > I’ve had this problem before and just did a quick > google search and copy and pasted a macro into > vba. Shouldn’t take more than 3 mins. > > Edit: > > Sub DeleteBlankRows3() > 'Deletes the entire row within the selection if _ > the ENTIRE row contains no data. > > Dim Rw As Range > If WorksheetFunction.CountA(Selection) = 0 Then > MsgBox “No data found”, vbOKOnly, “OzGrid.com” > Exit Sub > End If > With Application > .Calculation = xlCalculationManual > .ScreenUpdating = False > > > Selection.SpecialCells(xlCellTypeBlanks).Select > > For Each Rw In Selection.Rows > If > WorksheetFunction.CountA(Selection.EntireRow) = 0 > Then > Selection.EntireRow.Delete > End If > Next Rw > > .Calculation = xlCalculationAutomatic > .ScreenUpdating = True > End With > End Sub I didn’t try this macro but it’s hard to believe it works. First we make a selection and then we select in the selection? Say what? The idea is right though - I think you just need to specify a range instead.
CLICK F4, THEN GO TO SPECIAL, THEN BLANKS. AFTER IT JUST CLCIK DELTE ROW!!
Here is a way to sort, but not lose the order: You will need to create three new columns to the right. 1) In the first new column to the right, create an IF statement that shows a “1” if the row has data and a “0” if the row doesn’t. The first row will be 1 (assuming it has data). Every cell after that going down should have the formula “=IF(X=”",0,1) where X is the cell that would be blank or not blank. At this point, you should have 1s in the rows with data and 0s in the rows without. 2) In the next column to the right, the top cell would be 1 again. Then every cell going down should add the cell above to the cell directly to the right (either a 1 or 0 based on your formula from the first step outlined above). Now you have ascending numbers with repeats wherever there is no data. 3) In the third (and final) column to the right, the top cell would be a 1 once again. Every cell under it should contain the formula IF(cell directly to the left=cell to the left and up one cell, 0, cell directly to the left). Now you should have ascending numbers for all cells containing data. The cells containing no data should have 0s. 4) Sort by the rightmost column. Delete the blank rows. Should take about 2 minutes. Hope this helps.