Deleting Multiple Blank Rows

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
 
R

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
 
M

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
 
J

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
 
D

Don Guillett

select a1>f5>special>blanks>delete
or

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

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top