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

rahulmudiraj

New member
Joined
Oct 8, 2012
Messages
1
Reaction score
0
Points
0
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Functions cannot change other cells, including deleting them.
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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
 
Top