Results 1 to 4 of 4

Thread: How to delete a row based on a value in a column using IF Statement

  1. #1

    How to delete a row based on a value in a column using IF Statement



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I am new to excel and i need one small help, i have a data of 10 rows and 6 columns, if i delete the data in column 1 , i want the entire row 1 to get deleted

    and this i want using IF Statement

    can any one please help

    Regards
    Rahul

  2. #2
    Functions cannot change other cells, including deleting them.

  3. #3
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    You would need to create a macro for this.
    You would then use code similar to this.
    with activesheet
    if isblank(.range("A" & lRowCounter)) = true
    .range("A" & lRowCounter).entirerow.delete
    lRowCounter = lRowCounter - 1
    end with

  4. #4
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    Try using this, I simply made a command button and put this in it.

    Code:
    Private Sub CommandButton1_Click()
       'Purpose: to delete an entire row if a specific column has no data.
       'By Simi
       'created 10-8-12
       
       'declare variables
       Dim lRows As Long
       Dim lCurrentRow As Long
       lCurrentRow = 1
       
       With ActiveSheet
          lRows = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Row
          
          Do While lCurrentRow < lRows
             If IsEmpty(.Range("A" & lCurrentRow)) = True Then
                .Range("A" & lCurrentRow).EntireRow.Delete
                lRows = lRows - 1 'decrease total amount of rows because we deleted 1.
             Else
                lCurrentRow = lCurrentRow + 1 'increment to continue evaluating
             End If
          Loop
       End With
       
    End Sub

Posting Permissions

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