Results 1 to 6 of 6

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

Threaded View

  1. #1
    Registered User
    Join Date
    05-06-2013
    Location
    bangalore, india
    MS-Off Ver
    Excel 2010
    Posts
    28

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

    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!!!

    Sub DL()
    
     Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
             With ws
             ' Discarding first, last and transfer sheet
             If .Name <> "First Sheet" And .Name <> "Last Sheet" And .Name <> "Transfer sheet" Then
             ' To delete all blank rows in static sheets
             If .Name = "Static Sheet1" Or .Name = "Static Sheet2" Or _
            .Name = "Static Sheet3" Or .Name = "Static Sheet4" Then
             .Range("A5:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
             Else
             ' To delete all blank rows in dynamic sheets
             .Range("A7:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
             End If
             'To delete all blank rows in transfer sheet from cells below black row and beside black column
             'Else
             'If .Name = "Transfer sheet" Then
             End If
             On Error Resume Next
             End With
           
        Next ws
    End Sub
    Attached Files Attached Files
    Last edited by roshanvmech; 02-28-2014 at 11:36 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Program a conditional script that will delete entire row if cell in column B is empty
    By Rabbitstew in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-17-2012, 01:54 PM
  2. [SOLVED] Macro to delete entire row (within a specified range) if cell in column A is empty
    By instinctellekt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-07-2012, 10:01 AM
  3. Macro (Delete Entire row Cell in column R is empty)
    By andrewvt in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-20-2011, 07:06 AM
  4. Delete entire column if all cells in a certain range are empty
    By Andula in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-26-2011, 06:33 AM
  5. find <> empty cell in range, delete entire row
    By Shell_ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-26-2011, 12:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1