MrExcel.com - Your One Stop for Excel Tips & Solutions

Repeating an if function for say 300 cells

Posted by Chris Barford on February 24, 2001 7:04 AM

Here is the code i have in my macro:

If Range("J10") = "Y" Then
Rows("10:10").Select
Selection.Delete Shift:=xlUp
End If
If Range("J11") = "Y" Then
Rows("11:11").Select
Selection.Delete Shift:=xlUp
End If

Now how do make this code so it checks all the way down to row 300? The problem i am encountering is that i want the rows selected to be deleted and yet how can i get the row selected and then deleted. Thanks in advance
Chris

Posted by Dave Hawley on February 24, 2001 7:16 AM


Hi Chris

Run this code on the sheet you want checked.


Sub TryThis()
Dim DeleteCells As Range

With Sheet1
.AutoFilterMode = False
.Range("J9").AutoFilter
.Range("J9").AutoFilter Field:=1, Criteria1:="y"
Set DeleteCells = _
.Range("J10:J300").SpecialCells(xlCellTypeVisible)
DeleteCells.EntireRow.Delete
.AutoFilterMode = False
End With

End Sub

If you have blank cells in the range J10:J300 let me know and I'll modify it.

Dave


OzGrid Business Applications

Posted by Chris on February 24, 2001 9:14 AM

Wow i was having a bad case then, it didnt work but in the end i realised that it was because i had some text writen in J9. Thanks a lot your help is much appreciated.

Chris

Posted by Chris on February 24, 2001 9:28 AM

Doh no it doesnt-help

The code doesnt seem to do anything at all, the thing worked before because i had returned to my previous version and so got that one which works only up to a few cells that i could be bothered typing. Can you think of any reason, i renamed the sheet to sheet1 as well as changing the look value to Y and even tried it as y, there arent any empty cells in the area. What could be causing this problem can you think

Thanks again
Chris

PS i tried it on office 2000 and office 97

Hi Chris Sub TryThis() Dim DeleteCells As Range With Sheet1 .AutoFilterMode = False .Range("J9").AutoFilter .Range("J9").AutoFilter Field:=1, Criteria1:="y" Set DeleteCells = _ .Range("J10:J300").SpecialCells(xlCellTypeVisible) DeleteCells.EntireRow.Delete .AutoFilterMode = False End With End Sub

If you have blank cells in the range J10:J300 let me know and I'll modify it. Dave


Posted by Chris on February 24, 2001 10:22 AM

it didnt work, please could u look at the other post i made in this subject-nt-

Chris


Posted by Dave Hawley on February 24, 2001 10:29 AM

Re: Doh no it doesnt-help

Thanks again PS i tried it on office 2000 and office 97

Sorry Chris, I should have said I used the sheets Code Name not the Tab name. Use this one, it DOES use the sheet tab name.


With Sheets("Sheet1")
.AutoFilterMode = False
.Range("J9").AutoFilter
.Range("J9").AutoFilter Field:=1, Criteria1:="y"
Set DeleteCells = _
.Range("J10:J300").SpecialCells(xlCellTypeVisible)
DeleteCells.EntireRow.Delete
.AutoFilterMode = False
End With

End Sub

OzGrid Business Applications

Posted by Chris on February 24, 2001 11:20 AM

Yep that does the trick thanks a lot Dave

thanks u saved my ass, my IT projects in soon and that was the final bit of coding for it that i needed

Chris