How to delete entire row if first cell of it is empty for specific range and worksheets

**roshanvmech** · 02-28-2014, 11:16 AM

Hi I have a workbook as attached.
The work sheets are named as first page,last page, static sheet, dynamic sheet and transfer sheet. the dynamic sheets add up every day for this workbook.
My macro should Delete the entire row if its cell in column "A" is empty.
Restrictions
1. Macro should not work for first and last work sheets
2. The range for static sheets(should be from "A5" row to the last used row) is different from dynamic sheets(should be from "A7" row to the last used row)
3. In transfer sheet, the macro should run for rows below the black stripped row and also for the columns after the black stripped column if any cell from D8 is empty delete the entire column.
I have written the macro as below but when I run the macro, it is only working for static pages and when i select any dynamic page and re-run the macro, it is performing for all the dynamic pages. Also I am not able to select the range for transfer sheet and perform the macro.

Please help!!!

Please Login or Register  to view this content.

**Mumps1** · 02-28-2014, 12:47 PM

Just to clarify ... In the "Transfer sheet", if any cell in column A starting at row 25 is empty, you want to delete the entire row. Also if any cell in the range D8:AD8 is empty, you want to delete the entire column. Is this correct?

**Leith Ross** · 02-28-2014, 01:20 PM

Hello roshanvmech,

The macros below work for the static and dynamic sheets and leaves the First and Last sheets alone. It wasn't clear to me from the post or the workbook what to do with the Transfer sheet. If you provide a good example of which cells are to checked, I can then write the code for the macro. Here are the macros' code. These have been added to the attached workbook.

Macros are in the ThisWorkbook Module of the VBA Project

Please Login or Register  to view this content.

**roshanvmech** · 02-28-2014, 01:22 PM

Hi Mumps,
Yes it is correct. But it is not always row 25 or till column AD8, the rows and columns increase or decrease day by day i.e., they are dynamic. The only mark is that those start after the cell which has the interior color black.

**Mumps1** · 02-28-2014, 01:29 PM

Hi roshanvmech. I think that Leith has you covered.

**roshanvmech** · 03-01-2014, 12:36 PM

Hi Leith,
Your code is fantastic.
For transfer sheet, I want the rows to be deleted from the range starting from the row below the black strip. i.e., in this sheet the black strip is at row 24 and the range from which this macro should run is from row 25 to the last used row. The catch here is the black strip varies from workbook to workbook.
Similarly, for columns, the no. of columns differ from one workbook to another. I want the macro to delete the columns for which any cell between D8 to the last used column is empty, the entire column should be deleted.

How to delete entire row if first cell of it is empty for specific range and worksheets

LinkBack

Thread Tools

Rate This Thread

Display

How to delete entire row if first cell of it is empty for specific range and worksheets

Re: How to delete entire row if first cell of it is empty for specific range and worksheet

Re: How to delete entire row if first cell of it is empty for specific range and worksheet

Re: How to delete entire row if first cell of it is empty for specific range and worksheet

Re: How to delete entire row if first cell of it is empty for specific range and worksheet

Re: How to delete entire row if first cell of it is empty for specific range and worksheet

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Program a conditional script that will delete entire row if cell in column B is empty

[SOLVED] Macro to delete entire row (within a specified range) if cell in column A is empty

Macro (Delete Entire row Cell in column R is empty)

Delete entire column if all cells in a certain range are empty

find <> empty cell in range, delete entire row

Bookmarks

Bookmarks

Posting Permissions