alternative
Bhagyesh wrote on December 31, 1969 19:00 EST |
| we can use auto filter and delete the rows if we dont want to sort |
F5 key
Nimrod wrote on December 31, 1969 19:00 EST |
| Selecting certain types of cells is wht the "Goto" F5 key is for. Just Slect the columns in question , press F5 and then select "blanks" in the Goto window. Now only the blank cells are selected. Once Selected you can do anything you want with them ... eg right click and delete , or bold , or what ever. |
wrote on December 31, 1969 19:00 EST |
This not work when cell is containg untype characters
like NULL or formula ="" or something that similar but it is not really enpty cell. Very oftet in occure when copy from NULL from access by CTR + C and CTR + V. Export table feature work correctly and instead of NULL return really empty cell. |
Macro to delete rows
Gary L Brown wrote on December 31, 1969 19:00 EST |
'/============================/
' Sub Purpose: Delect all blank ROWS within the active cell's
' Used Range
'
Public Sub DeleteBlankRows()
Dim dbMaxRow As Double, dbMinRow As Double, i As Double
Dim dbMaxCol As Double
Dim rng As Range
On Error Resume Next
'only look in used area of the worksheet where active cell is
Set rng = Selection.Parent.UsedRange
'calculate area to be searched for blank rows
dbMaxRow = rng.Rows.Count '# of rows in used area
dbMinRow = rng.Cells(1, 1).Row '1st row in used area
dbMaxCol = rng.EntireColumn.Count '# of columns in used area
For i = dbMaxRow To dbMinRow Step -1
If IsError(rng.Cells(1, 1).Offset(i - 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count) Then
Else
If rng.Cells(1, 1).Offset(i - 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count = dbMaxCol Then
rng.Cells(1, 1).Offset(i - 1, 0).EntireRow.Delete
End If
End If
Next i
Set rng = Nothing
End Sub
'/============================/
' |
Pitfall
Balkee wrote on December 31, 1969 19:00 EST |
| Depending on the contents of the first column in the range, the sorting will cause the order of rows to be different from the original order. After you delete the blank rows, it may be a bigger hassle getting back to the original order. Use filter as Bhagyesh suggested. |
I found that
Rhobbynho wrote on December 31, 1969 19:00 EST |
'Matt Neuburg, PhD http://www.tidbits.com/matt Aug , 1998
Sub DelEmpty()
Selection.SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)
End Sub |
Agreed
Ade wrote on December 31, 1969 19:00 EST |
| Really no need to use sort instead of go to. A macro is a good idea if you want to include it in the code you write. |
Easier than that
c7borg wrote on December 31, 1969 19:00 EST |
just press f5 select special and check the "blanks" radio button then simply select edit>delete
job done |
Alternative to deleting blank rows
Roger Morehouse wrote on December 31, 1969 19:00 EST |
| ALthough I agree that AutoFilter works great for some worksheets, a large worksheet will bog down using this method. If you need to maintain the order of the rows, insert a new column, fill with numbers, sort by desired column, delete blank rows, sort by number column, delete number column. |
Alternate to Sorting, then deleting
Roger Morehouse wrote on December 31, 1969 19:00 EST |
| For large worksheets, another method is to insert a column, then fill with numbers. Sort by the desired column, delete the rows, resort by the number column. But I agree, Filter is good for small worksheets. |
AWESOME
dude wrote on December 31, 1969 19:00 EST |
| you are so good with the Macro - i took 45 mins to do this manually in the morning - and your macro did it in 3 seconds flat -- THANK YOU FOR THIS <SALUT!> |
Retire Young, Retire Rich
