Macro to remove the blank rows and detect header

**coolanks1** · 03-19-2009, 03:05 AM

Dear All,

I have uploaded a sample excel sheet wherein i would require to detect the header which is row 4 and remove all blank rows like row 5,8,9...etc.
The purpose is to get the file into simple format which is row1 header followed by consolidated non blank rows.

Hope i have made myself very clear and would anticipate your expert advices on the same.

Thanks
Ankur

**JBeaucaire** · 03-19-2009, 03:08 AM

Press Alt-F11 to open the VBeditor
Click on INSERT > MODULE
Paste in this code:

Please Login or Register  to view this content.

Press Alt-F11 to close the editor
Save your sheet.

Press Alt-F8 and select the new macro to run it.

**royUK** · 03-19-2009, 03:16 AM

Try

Please Login or Register  to view this content.

**coolanks1** · 03-19-2009, 04:05 AM

thanks a lot.....u guys rock :-)

**JBeaucaire** · 03-19-2009, 11:30 AM

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

**coolanks1** · 03-20-2009, 02:54 AM

I have one more request before i close this thread, how can i make this macro run for all tabsheets available , in other words the macro should do the same operations for all the tab sheets in that particular workbook.

Look forward for an early reply

Ankur

**royUK** · 03-20-2009, 03:28 AM

You haven't said which macro you are using, also is the data laid out exactly the same in each sheet? If it is

Please Login or Register  to view this content.

**coolanks1** · 03-20-2009, 03:40 AM

Sorry for not providing the information earlier, while running your code i got an error. I have also attached the screen shot for better understanding.Please lemme know the solution

Thanks for your reply.

Ankur