delete gap lines

[majidsiddique]

Dear,

i have problem in excel. how i can remove quickly line delete in S.No column (B). there is a gap between lines, excel can identify automatic gap line and delete them immediately. data is too lengthy. only S.No column(B) is main criteria in a sheet.



[TABLE="width: 334"]
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]S.No[/TD]
[TD]Description [/TD]
[TD]Lot[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]12010[/TD]
[TD]1[/TD]
[TD]teri towel[/TD]
[TD]12111110 [/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2[/TD]
[TD]teri towel[/TD]
[TD]12111110[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]3[/TD]
[TD]teri towel[/TD]
[TD]12111110[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]4[/TD]
[TD]teri towel[/TD]
[TD]12111110[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]5[/TD]
[TD]teri towel[/TD]
[TD]12111110[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]6[/TD]
[TD]teri towel[/TD]
[TD]12111110[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]7[/TD]
[TD]teri towel[/TD]
[TD]12111110[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]8[/TD]
[TD]teri towel[/TD]
[TD]12111110[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30010[/TD]
[TD]1[/TD]
[TD]wingles[/TD]
[TD]2001889[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2[/TD]
[TD]wingles[/TD]
[TD]2001889[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]3[/TD]
[TD]wingles[/TD]
[TD]2001889[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]4[/TD]
[TD]wingles[/TD]
[TD]2001889[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]5[/TD]
[TD]wingles[/TD]
[TD]2001889[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]6[/TD]
[TD]wingles[/TD]
[TD]2001889[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]7[/TD]
[TD]wingles[/TD]
[TD]2001889[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]8[/TD]
[TD]wingles[/TD]
[TD]8899888[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]9[/TD]
[TD]wingles[/TD]
[TD]8899888[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]40010[/TD]
[TD]1[/TD]
[TD]driver22[/TD]
[TD]55551[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2[/TD]
[TD]driver22[/TD]
[TD]55551[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]3[/TD]
[TD]driver22[/TD]
[TD]66662[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]5[/TD]
[TD]driver22[/TD]
[TD]66662[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

If the "gap" in Column B is a truly blank cell (absolutely nothing in it), then this macro should work fairly quickly...

Sub DeleteGapRows()
  Columns("B").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[majidsiddique]

Thanks Rick,

it works properly. thanks a lot.

Majid

 

[majidsiddique]

Dear Rick,

is it possible to remove any cell value selected gap lines first then apply to delete line? like selected gap lines volume D cell value " hhhh " and E " yuuu"

kindly tell me.

Thanks