Deleting Multiple Blank Rows

  • Thread starter Thread starter Diabolo_Devil
  • Start date Start date
D

Diabolo_Devil

Is there a quick and simple way of deleting multiple different blank rows
from a sheet? - Other than indivdually clicking on them (with ctrl) and then
deleting.

i.e. removing the blank rows between the other rows of data? (But on a large
scale)

Number Name
B5298 BOWER, RH

G9005 LANDA J
G9456 BHAKRI H

J1482 NIRMAL KUMAR.R

T9574 BAMBER A
 
Hi

Select the whole range of data>Sort>Ascending>my data has Headers>Column A
and all the blank rows will be shifted to the end
 
How about a macro

Alt+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and
paste the code in. Change Sht to the correct sheet and run the code


Sub Blank_Rows()
Dim i As Long
Set Sht = Sheets("Sheet1")' Change to suit
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
lastrow = Sht.Cells(Rows.Count, "A").End(xlUp).Row
For i = lastrow To 1 Step -1
If WorksheetFunction.CountA(Sht.Rows(i)) = 0 Then
Sht.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
If you don't mind sorting the data - this is the easiest way to do what you
are after.
Micky
 
select a1>f5>special>blanks>delete
or

Sub deleteblankrows()
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
 
hi Diabolo_Devil,

i think you can use filter function for deleting the blank rows.
select the entire range of your data, click on data, filter, auto filter.
from the downward arrow shown on the first row, select the blanks and delete
the entire row/s.

i think this should solve your problem

learn - n - enjoy
 
Select a column of your data area which identifies the blank rows
(probably your number column if that is a unique ID of some sort)

GoTo Special (Ctrl-G, Alt-S), click on Blanks, OK
Now all the blank cells are selected, right click one of them (NOT a
row), choose delete, answer "rows" whcn prompted what to delete.
(or go to the Home ribbon > Cells > Delete > Rows.)

Hope this helps.
Adam
 
Back
Top