IF Then Loop Macro

[fboyd]

I have a small problem I hope you can assist with.

I have a workbook with a couple command buttons.
The first command button has 5 macros.
The first imports a worksheet from another workbook into sheet 3 of the original workbook. This worksheet list 86,300 computers with 46 columns of information on each computer.
The second clears all rows which do not pertain to our division.
Third clears all rows which are outside he USA.
The fourth sorts the remaining data putting the blank rows at the bottom of the spreadsheet.
The fifth sets sheet one as the active worksheet.

The macros assigned to this command button do exactly as I want and performs within 45 seconds.
Leaving 19,300 rows of data.

The second through fifth command buttons contains 2 macros each. Depending on which command button is selected, there will be a different name listed as the IT Person.
The first (below) is supposed to clear all rows which do not contain a specific IT person
listed in column #34 (AH). My problem is that after running this command button all rows are deleted. In line seven, the name of the IT person, has been pasted into the macro to maintain accuracy.

Worksheets("Hardware Detail").Select
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 11 Step -1 'Header consist of rows 1 through 10
    If UCase(Cells(r, 34).Value) <> "IT Person" Then
        Rows(r).Clear
    End If
Next r
 
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Worksheets("Instructions").Select

The second sorts the remaining data placing the blank rows at the bottom of the worksheet.

 

[Jonmo1]

This line will ALWAYS be true

If UCase(Cells(r, 34).Value) <> "IT Person" Then

Because Ucase is capitalizing the value in the cell..

so if the cell is John Doe, that translates to

If "JOHN DOE" <> "John Doe" <- this of coarse is true..


Perhaps you want

If UCase(Cells(r, 34).Value) <> "IT PERSON" Then

 

[Greg Truby]

When you're pasting the name, are you making sure you paste it in as uppercase? JFYI, if you are clearing the row, you needn't loop backwards (stepping by -1). You only need to loop up the worksheet if you're deleting rows.

 

[fboyd]

I worked on this for hours and hours..

Such a simple fix.. Thanks so much for your assitance and the tip..

 

[Jonmo1]

Glad to help, thanks for the feedback

 

[fboyd]

As I mentioned in my original post. I was able to delete most of the rows in the imported workbook by comparing the division then the country. I then decided to go further. And delete the states that I did not care about. It hit me that I was going about ths all wrong.

Why not look for the specific ITPERSONS and delete all other rows..

The macro you assisted with above helps out greatly when I need to isolate a specific ITPERSON. So, I tried to expand this into an IF OR type macro.

Here's what I came up with.. Which of course does not work.

Sub Delnon()
Worksheets("Hardware Detail").Select
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 11 Step -1
If UCase(Cells(r, 34).Value) <> "ITPERSON1"

Or UCase(Cells(r, 34).Value) <> "ITPERSON2"

Or UCase(Cells(r, 34).Value) <> "ITPERSON3"

Or UCase (Cells(r, 34).Value) <> "ITPERSON4"

Or UCase(Cells(r, 34).Value) <> "ITPERSON5" Then

Rows(r).Delete
End If
Next r

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Worksheets("Instructions").Select
End Sub



Where am I going wrong??

 

[jmthompson]

Try

Sub Delnon()
Worksheets("Hardware Detail").Select
Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    lastrow = ActiveSheet.UsedRange.Rows.Count
    For r = lastrow To 11 Step -1
        If UCase(Cells(r, 34).Value) <> "ITPERSON1"
        
        And UCase(Cells(r, 34).Value) <> "ITPERSON2"
        
        And UCase(Cells(r, 34).Value) <> "ITPERSON3"
        
        And UCase (Cells(r, 34).Value) <> "ITPERSON4"
        
        And UCase(Cells(r, 34).Value) <> "ITPERSON5" Then
        
        Rows(r).Delete
        End If
    Next r
   
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Worksheets("Instructions").Select
End Sub

 

[fboyd]

When I put all of the AND statements on the same line, it worked beautifully.
I changed the Delete to a Clear and it works in seconds insteand of minutes..

Now on to comparing the 2 sheets to find the differences..

Thanks ever so much..

Fred

 

[Greg Truby]

Depending on the frequency of turnover in your IT list and on the long-term support strategy, you might wish to consider dropping the names into a list and using ISNUMBER(MATCH()) or a hidden helper column on the sheet itself that you would then scan for. This would move list maintenance out of the code and into the worksheet where it would be much easier for a non-coder to do upkeep.

 

[fboyd]

That sounds like a good idea, Something else to consider as I continue with this project.

Thanks
Fred