PDA

View Full Version : Delete row on condition



Conor
11-10-2007, 10:46 AM
Hi Guys,
Wondering if you guys can help me out. I am looking for a macro that will check if a cell in column A is empty, check if it's counterpart in column I is empty and if both are empty, delete that entire row.

Many thanks in advance :bow:

Bob Phillips
11-10-2007, 10:56 AM
Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If .Cells(i, "A").Value = "" And _
.Cells(i, "I").Value = "" Then
.Rows(i).Delete
End If
Next i

End With

End Sub

Zack Barresse
11-10-2007, 11:34 AM
Or you could not loop...

Sub DeleteBlankRows()
Dim ws As Worksheet, rngWhole As Range, rngFilter As Range, lastRow As Long
Call ToggleEvents(False)
Set ws = ActiveSheet 'set as necessary
lastRow = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
ws.Range("J:J").Insert
Set rngWhole = ws.Range("A1:J" & lastRow)
Set rngFilter = ws.Range("A2:J" & lastRow)
ws.Range("J1").value = "TempHeader"
ws.Range("J2:J" & lastRow).Formula = "=AND(LEN(A2)=0,LEN(I2)=0)"
rngWhole.AutoFilter field:=rngFilter.Columns.Count, Criteria1:="TRUE"
rngFilter.SpecialCells(xlCellTypeVisible).EntireRow.Delete
ws.Range("J:J").Delete
ws.AutoFilterMode = False
Call ToggleEvents(True)
End Sub

Sub ToggleEvents(blnState As Boolean)
'Originally written by firefytr
With Application
.DisplayAlerts = blnState
.EnableEvents = blnState
.ScreenUpdating = blnState
If blnState Then .CutCopyMode = False
If blnState Then .StatusBar = False
End With
End Sub

Conor
11-11-2007, 05:30 AM
Thanks very much guys. Was also wondering how i would check if the contents of a cell begin with a digit instead of a character or symbol?

Bob Phillips
11-11-2007, 06:39 AM
If Isnumeric(Left$(.Cells(i, "A").Value,1)) Then

PacManiac
11-11-2007, 03:31 PM
A couple of questions/comments, if I may.

1. xld -- won't iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row miss the last row(s) if they're blank in column A?

2. Zack -- I got an error with ws.Range("J:J").Delete, "cannot delete in AutoFilter mode".

HTH :)

Bob Phillips
11-11-2007, 04:18 PM
A couple of questions/comments, if I may.

1. xld -- won't iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row miss the last row(s) if they're blank in column A?

Nope, it works its way from the bottom of the sheet until it gets a row with data.

PacManiac
11-11-2007, 10:32 PM
Nope, it works its way from the bottom of the sheet until it gets a row with data.
Hmm. Tried it with data in the top 4 rows but with cols A & I blank in rows 2 & 4, it deleted the 2nd row only. (MsgBox iLastRow = 3)

Guess I must be missing something. Sorry.

Bob Phillips
11-12-2007, 01:23 AM
Not this bit



For i = iLastRow To 1 Step -1

PacManiac
11-12-2007, 07:30 AM
No, I pasted it into a module "wholesale". I wouldn't tinker with 1 Step -1 since I've no idea what it means.

But again, I filled A1:I6 except I left A & I blank in rows 2, 4, 5 & 6. Ran the code and it only deleted row 2.

:(

Bob Phillips
11-12-2007, 08:09 AM
Well, I just entered 1,3,8,9,10,11,12,13,14,15,16 in the appropriate rows, ran the code, and it deleted them all.

Are you sure that some of those cells don't have spaces, not blank?

PacManiac
11-12-2007, 08:17 AM
Positive -- it was a fresh wb.

Bob Phillips
11-12-2007, 08:27 AM
Post it then and let's see.

PacManiac
11-12-2007, 08:33 AM
Sorry, I'm new here.

Here you go. :)

Bob Phillips
11-12-2007, 09:06 AM
LOL! That is not how youy described it, in your stated example you had data in 7-16, here you don't.

The problem is that it tests column A for the last value, probably not a good idea with that being one of the criteria columns.

This should be better



Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = Range("A1").CurrentRegion.Rows.Count
For i = iLastRow To 1 Step -1
If .Cells(i, "A").Value = "" And _
.Cells(i, "I").Value = "" Then
.Rows(i).Delete
End If
Next i

End With

End Sub

PacManiac
11-12-2007, 02:58 PM
I guess it might look like data to (row) 16 in my stated example, it's actually a1:i6. :)

Zack: w/r/t "Or you could not loop"-- how about:

Sub test()
Application.ScreenUpdating = False
Columns(1).Insert
Columns(1).FormulaR1C1 = "=RC[1]&RC[9]"
Columns(1).Value = Columns(1).Value
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns(1).Delete
Application.ScreenUpdating = True
End Sub

(? -- sorry, don't know how to do the "VBA:" thing :o: )

Zack Barresse
11-13-2007, 11:59 PM
Well, it is a bit different from what I posted. I guess if you like it...

camealion
11-15-2007, 01:50 PM
Hello All,

I've been trying the suggestions that you gave Conor and trying to utilize it for my situation. So to lay it out, I need to delete a row if it matches another row on a different worksheet. The critera is if it matches serveral columns of data. So I've modified the some of the code you guys have given and my problem lies in that it works fine if the rows are in the same order exactly and I have a sort on them, but if there not in the same order or if there is missing rows it won't delete the duplicates.

Any suggestions Thanks in advance


Public Sub delOLD()
Dim i As Long
Dim iLastRow As Long
Dim ws As Worksheet, ws1 As Worksheet

Set ws = Sheets("Data")
Set ws1 = Sheets("Old Records")

'ws1.Visible = xlSheetVisible

With ws

iLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = iLastRow To 1 Step -1
If .Cells(i, "A").Value = ws1.Cells(i, "A").Value And _
.Cells(i, "B").Value = ws1.Cells(i, "B").Value And _
.Cells(i, "C").Value = ws1.Cells(i, "C").Value And _
.Cells(i, "D").Value = ws1.Cells(i, "D").Value And _
.Cells(i, "E").Value = ws1.Cells(i, "E").Value And _
.Cells(i, "F").Value = ws1.Cells(i, "F").Value And _
.Cells(i, "G").Value = ws1.Cells(i, "G").Value And _
.Cells(i, "H").Value = ws1.Cells(i, "H").Value And _
.Cells(i, "I").Value = ws1.Cells(i, "I").Value And _
.Cells(i, "J").Value = ws1.Cells(i, "J").Value And _
.Cells(i, "K").Value = ws1.Cells(i, "K").Value And _
.Cells(i, "L").Value = ws1.Cells(i, "L").Value And _
.Cells(i, "M").Value = ws1.Cells(i, "M").Value Then
.Rows(i).Delete
End If
Next i

End With

'ws1.Visible = xlSheetHidden
'hddr

End Sub


P.S. I hope this is going to the right thread sorry if it don't.

camealion
11-16-2007, 12:00 PM
I have solved the problem with the help from Ozgrid

final code"


Public Sub delOLD()
' Ozgrid Post# 399354
Dim i As Long
Dim iLastRow As Long, xLastRow As Long
Dim ws As Worksheet, ws1 As Worksheet

Set ws = Sheets("Data") ' Imported Data
Set ws1 = Sheets("Old Records") 'Deleted Data

'ws1.Visible = xlSheetVisible
xLastRow = ws1.Range("A65536").End(xlUp).Row
With ws
iLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = iLastRow To 1 Step -1
For x = xLastRow To 1 Step -1
If .Cells(i, "A").Value = ws1.Cells(x, "A").Value And _
.Cells(i, "B").Value = ws1.Cells(x, "B").Value And _
.Cells(i, "C").Value = ws1.Cells(x, "C").Value And _
.Cells(i, "D").Value = ws1.Cells(x, "D").Value And _
.Cells(i, "E").Value = ws1.Cells(x, "E").Value And _
.Cells(i, "F").Value = ws1.Cells(x, "F").Value And _
.Cells(i, "G").Value = ws1.Cells(x, "G").Value And _
.Cells(i, "H").Value = ws1.Cells(x, "H").Value And _
.Cells(i, "I").Value = ws1.Cells(x, "I").Value And _
.Cells(i, "J").Value = ws1.Cells(x, "J").Value And _
.Cells(i, "K").Value = ws1.Cells(x, "K").Value And _
.Cells(i, "L").Value = ws1.Cells(x, "L").Value And _
.Cells(i, "M").Value = ws1.Cells(x, "M").Value Then
.Rows(i).Delete
End If
Next x
Next i
End With

'ws1.Visible = xlSheetHidden
'hddr

End Sub