Deleting Multiple Blank Rows

[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

 

[Roger Govier]

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

 

[Mike H]

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

 

[Jan Karel Pieterse]

Hi Diabolo,

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)

You could sort the table?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[Don Guillett]

select a1>f5>special>blanks>delete
or

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

 

[YESHWANT]

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

 

[AdamV]

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