+ Reply to Thread
Results 1 to 6 of 6

VBA to delete empty rows

  1. #1
    Registered User
    Join Date
    07-15-2006
    Posts
    43

    VBA to delete empty rows

    Hi,

    I am trying to delete rows that are zero or blank. I am not sure why the various VBA codes I have tried doesn't work. It may have to do with the cells having formulas.

    In the attached file excerpt, I am trying to delete rows with zero or blank in column P. However, column P has formulas.

    The actual sheet would have over 6,500 rows.

    Thanks.
    Attached Files Attached Files
    Last edited by Borg; 09-25-2010 at 10:53 AM.

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to delete empty rows

    Hi Borg
    Perhaps this
    Please Login or Register  to view this content.
    Of course try on test data as it may be deleting more than you wish.

    John
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    07-15-2006
    Posts
    43

    Re: VBA to delete empty rows

    Works perfect. Thanks.

  4. #4
    Registered User
    Join Date
    06-28-2010
    Location
    Midwest
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: VBA to delete empty rows

    Hi John,

    I have a similar requirement, where in I am trying to delete all rows, if cell value in column F is blank. There are about 3000 rows of records, data sorted with blank columns in the end of the table.

    I tried this formula, as my column header is occupying till row 4. Records start from Row 5 onwards, and Column F may have few blank cells from F1:F4, which I do not want to delete.

    Any advise, would be of great help!

    Thank much in advance.

    ======================

    Dim LR As Long
    LR = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row

    ActiveSheet.AutoFilterMode = False
    ActiveSheet.Range("F8:P" & LR).AutoFilter Field:=1, Criteria1:="=0.00", _
    Operator:=xlOr, Criteria2:="="
    Range("PF8:P" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    End Sub[/code]


    Quote Originally Posted by jaslake View Post
    Hi Borg
    Perhaps this
    Please Login or Register  to view this content.
    Of course try on test data as it may be deleting more than you wish.

    John

  5. #5
    Registered User
    Join Date
    06-28-2010
    Location
    Midwest
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: VBA to delete empty rows

    Greetings!

    I am trying to delete all rows of a excel table, if cell value in 'Column F5' onwards is blank.

    There are more than 3000 rows of records and data is sorted with blank columns at the end of the table.

    For example:

    In the attached .jpeg file, column C has blank cells from C9:C, and hence would like to DELETE all the rows from 9 through 3000 rows.

    Any advise, would be of great help!

    Thank much in advance.
    Attached Images Attached Images

  6. #6
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to delete empty rows

    Hi excelrequest


    Two things:
    1. It's frowned upon to "piggy back" another's thread. If you feel the other thread is relevant, provide a link to such thread in yours.
    2. Please use code tags around your code.
    I'll be glad to look at this with you after you've done so.

    John

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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