delete rows with blank cells

[pani_hcu]

Dear All,

I want to delete the rows which have consecutive 4 blank cells. There are thousands of rows to check and delete. How to do it fast and efficiently?

Looking forward for your suggestion.

With sincere regads,
Upananda

[NeedForExcel]

Are the 4 blank rows specific to some column?

[pani_hcu]

Yes it is specific to all the columns in the worksheet which has data as per the company names given in the first column A.

[NeedForExcel]

But how can that be tackled! Think of it this way, if column B has 4 Blank rows and Column C has 5 blank rows (4 On same rows as on Column B) if my code detects 4 blank rows on B column and deletes those rows, then Column C will now have only 1 Blank row. Which now wont be detected by the code and will be left alone..

[pani_hcu]

I want to read the data by rows as every row represent specific company. Column represents the year. To make it easy i want the first five column of the data i.e, B-F. sorry for not being clear in my previous reply.

[NeedForExcel]

I want to delete the rows which have consecutive 4 blank cells.

Then I guess you mean you want to Delete the Company if, 1st 5 Columns are blank.. Is that right?

For example, Row No 48 (Raj Oil Mills Ltd.), should be deleted as Cells (B48,C48,D48,E48,F48) are all blank. Is that right?

[pani_hcu]

Yes sir, your are right.

[NeedForExcel]

You dont really need VBA for that.

Insert a Column between G & H Columns and put this formula on Cell G5.

=B5&C5&D5&E5&F5

And Drag it down till the last row.

Then apply AutoFilter & Look for Blanks (The last option) and then get rid of the rows..

Hope this helps!

If you still need VBA, let me know