Selecting/deleting empty cells

  • Thread starter Thread starter Talitha
  • Start date Start date
T

Talitha

Is there a way I can select and delete all empty cells,
rows and columns all at once to leave me with only the
cells that have something in it? Right now I am
selecting them manually and it's taking forever! Please
help!
 
Hi Talitha

choose
edit / go to / special
choose blanks
say OK
then choose edit / delete and what you want to do

let us know how you go

Cheers
JulieD
 
Hi

Select a column. Hit F5 and select Special and then Blanks. Right click on
one of the blanks and select Delete . . .and in the next dialog box click
Entire Row.
 
Thanks so much. It worked perfectly!

Talitha
-----Original Message-----
Hi

Select a column. Hit F5 and select Special and then Blanks. Right click on
one of the blanks and select Delete . . .and in the next dialog box click
Entire Row.

--
Andy.





.
Click to expand...
 
Hi this macro will delete any empty rows or empty columns.
I'm not sure where you would want to place any odd blank
cells, filled from below? or right?

Sub testdel()
Dim nr As Long, r As Long, count As Long
Dim nc As Integer, c As Integer
Dim rng As Range
Dim v
Selection.SpecialCells(xlCellTypeLastCell).Select
nr = ActiveCell.Row
nc = ActiveCell.Column
For c = 1 To nc
Set rng = Range(Cells(1, c), Cells(nr, c))
count = 0
For Each v In rng
If IsEmpty(v) Then
count = count + 1
End If
Next v
If count = nr Then
rng.EntireColumn.Delete
End If
Next c
Selection.SpecialCells(xlCellTypeLastCell).Select
nr = ActiveCell.Row
nc = ActiveCell.Column
For r = 1 To nr
count = 0
Set rng = Range(Cells(r, 1), Cells(r, nc))
For Each v In rng
If IsEmpty(v) Then
count = count + 1
End If
Next v
If count = nc Then
rng.EntireRow.Delete
End If
Next r

End Sub

Press Alt+ F11 to open VB Editor,choose insert macro and
paste the code

You can run it from the VB Editor (Press F5) or return to
the sheet and press Alt + F8, select the macro and choose
Run. MAke sure that you are in the correct sheet.


(e-mail address removed)


regards
Peter
 
Back
Top