Macro for removing rows of empty cells within a spreadsheet

Hi,

Novice Excel user who needs help with hopefully a simple problem.

The attached file is a portion of my original which is far too large to upload but should show my issue. It shows an area of cells within my spreadsheet which are automatically populated from other cells in the workbook.

The idea is that at the end of the week once the data has been gathered it can be copied and pasted directly into another piece of software. Unfortunately because there is blank rows of cells between the data it will only paste the first block of numbers.

Is there a simple macro that can identify the empty rows within a set area of cells (blue area B4 to K78), strip them out and move the data up? Additionally I'd require a macro to reset the cells back again once the copy and paste had been completed.

The important bit to add is that on the original spreadsheet there is numerous other data/formulas alongside this area therefore the macro won't be able to strip out an entire row but just the row of cells within the blue area shown.

Any help gratefully appreciated.

For each row that is empty in column B, this would work:

Please Login or Register  to view this content.

In this instance, since many of the rows I believe you want do delete in fact contain the value 0, a loop with step-1 would work:

Please Login or Register  to view this content.

Thanks for all the advice given. On the basis of what's been said I've made some alterations to my worksheet.

The area of data I wanted to remove rows of cells from has now been transferred to another worksheet so it doesn't interfere with any other formulas nearby. This has allowed me to create a macro to remove entire rows.

In the first column where the cells had no data but just zeros I added a filter to remove those which subsequently deleted the empty rows. The macro was set up on the basis of this and subsequently could be setup in reverse to reinstate the rows.

Thanks again for the help.