Blank Rows deletion

[amit dani]

Hiii Guys,

I have few excel sheets each containing more than 60000 rows of data. Issue is that after each row there is an blank row.. i have tried to delete by Ctrl + G method, but it says "data range is complex...". I have tried VBA also but every time excel hangs...Can you guys suggest some other ways????

Thanks

 

[Marc L]

Hi,

if there are merged cells in a row to delete, unmerged them and no more issue !​

 

[amit dani]

Yes there were merged cells.. but i am trying after unmerging them only....

 

[Xiq]

Possible to sort your data? (I guess not, but worth a try)

This way your selection of blank should become a contiguous range, instead of all over.

 

[ThrottleWorks]

Hi Amit,

could you please try this link if possible.

http://stackoverflow.com/questions/9379673/excel-vba-delete-empty-rows

I am also trying to incorporate this code at my PC, will share the results.

 

[oldchippy]

Hi,

When you say you unmerged them, did you select the whole sheet by clicking on the square in the top left adjacent to column A/Row 1?

 

[amit dani]

@Xiq...tried that also...but these blank rows are quite stubborn I must say...they are not going anywhere....

@ThrottleWorks :- I tried below code...its working fine for small range...but can't handle large data...

Sub RemoveEmpyRows()
' this macro will remove all rows that contain no data
Dim i  As Long
Dim lLastRow As Long
lLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Application.ScreenUpdating = False
For i = lLastRow To 1 Step -1
 If WorksheetFunction.CountA(ActiveSheet.Rows(i)) = 0 Then
  ActiveSheet.Rows(i).EntireRow.Delete
 End If
Next i
Application.ScreenUpdating = True
End Sub

I tried macro which you mentioned also...but still same problem...

 

[amit dani]

@oldchippy :- yes i selected whole sheet...

 

[ThrottleWorks]

Hi Amit,

could you please try this,

http://www.mrexcel.com/forum/excel-questions/603354-visual-basic-applications-delete-blank-rows.html

Sub delete()
Application.ScreenUpdating = False
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.delete
Application.ScreenUpdating = True
End Sub

It is working for me till 30,000 rows, takes around 2 minutes.

 

[amit dani]

@ThrottleWorks : i am getting below error..

 

[amit dani]

Hii guys.. I think i Found the solution...i converted data into table...and then I removed duplicates...
Thanks you guys for your help....

 

[ThrottleWorks]

Hi Amit, thanks for the feedback. Glad it is working.

I did not even thought about "Remove Duplicates" really nice.

I tried something, it is far from "being elegant", I think the main issue was with the amount of data.
Excel was getting hang due to large range.

I tried this code with 60,000 rows, worked for me, not able to upload it, file size too heavy.

Happy new year, have a nice year ahead.

 

[amit dani]

thanks man...you took quite lot of trouble for me...wish you a awesome year ahead...

 

[ThrottleWorks]

@amit dani , thanks for the greetings, wish you the same.