Deleting Multiple Blank Rows
 

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

Deleting Multiple Blank Rows
 

Hi

Select the whole range of dataSortAscendingmy data has HeadersColumn A
and all the blank rows will be shifted to the end
--
Regards
Roger Govier

Diabolo_Devil wrote:
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

Deleting Multiple Blank Rows
 

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.


"Diabolo_Devil" wrote:

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

Deleting Multiple Blank Rows
 

If you don't mind sorting the data - this is the easiest way to do what you
are after.
Micky


"Diabolo_Devil" wrote:

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

Deleting Multiple Blank Rows
 

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

Deleting Multiple Blank Rows
 

select a1f5specialblanksdelete
or

Sub deleteblankrows()
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Diabolo_Devil" wrote in message
...
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

Deleting Multiple Blank Rows
 

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



On 10/03/2010 13:42, Don Guillett wrote:
select a1f5specialblanksdelete
or

Sub deleteblankrows()
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete
End Sub