+ Reply to Thread
Results 1 to 2 of 2

Delete a row if blank cell

  1. #1
    mohd21uk via OfficeKB.com
    Guest

    Delete a row if blank cell

    I would like to delete a row if the the value in colmn I and H in the
    respective row is blank. I would like to know if a macro can do this from the
    start to the end of the worksheet.

    Thanks.

    --
    Message posted via OfficeKB.com
    http://www.officekb.com/Uwe/Forums.a...excel/200605/1

  2. #2
    Kevin B
    Guest

    RE: Delete a row if blank cell

    The following macro will work if the data is in Sheet1 and column A has
    values in the first row through the last. If Column A is blank it exits the
    loop and stops the macro, otherwise it checks the values in columns I and H
    and if both are blank it deletes the row.
    I've tested this some and it worked okay.

    Sub RemoveRows()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lngRow As Long
    Dim lngRowOffset As Long
    Dim intColOffset As Integer
    Dim lngCurrentRow As Long
    Dim varVal1 As Variant
    Dim varVal2 As Variant
    Dim varVal3 As Variant
    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets("Sheet1")

    ws.Activate
    Range("A1").Select
    varVal3 = ActiveCell.Offset(lngRowOffset).Value
    Application.ScreenUpdating = False

    Do Until varVal3 = ""
    varVal1 = ActiveCell.Offset(lngRowOffset, 7).Value
    varVal2 = ActiveCell.Offset(lngRowOffset, 8).Value
    lngCurrentRow = lngRowOffset + 1
    If varVal1 = "" And varVal2 = "" Then
    ws.Rows(lngCurrentRow & ":" & lngCurrentRow).Delete
    End If
    lngRowOffset = lngRowOffset + 1
    varVal3 = ActiveCell.Offset(lngRowOffset).Value
    Loop

    Set wb = Nothing
    Set ws = Nothing
    Application.ScreenUpdating = True

    End Sub
    --
    Kevin Backmann


    "mohd21uk via OfficeKB.com" wrote:

    > I would like to delete a row if the the value in colmn I and H in the
    > respective row is blank. I would like to know if a macro can do this from the
    > start to the end of the worksheet.
    >
    > Thanks.
    >
    > --
    > Message posted via OfficeKB.com
    > http://www.officekb.com/Uwe/Forums.a...excel/200605/1
    >


+ 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