Solved

Excel Macro to find computer name start by and delete row

Posted on 2015-01-23
10
154 Views
Last Modified: 2015-01-23
Hi,

Need your help to have a macro that will search for a string that start by "MAX-" or "BNC-" in column A, for "#N/A in column B and when the match is found, delete the row.

Can you help?
0
Comment
Question by:mldaigle1
  • 5
  • 3
  • 2
10 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40566516
Does it have to be a macro or can it be done otherwise?
0
 

Author Comment

by:mldaigle1
ID: 40566524
it has to be a macro, it will be call up in a clean up phase
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 250 total points
ID: 40566557
Sub DelMAXBNCNA()
    ActiveSheet.Range("A1").EntireRow.Insert
    Range("A1:B1").Value = Split("mb,NA", ",")
    Range("A1").AutoFilter Field:=1, Criteria1:="=MAX-*", Operator:=xlOr, Criteria2:="=BNC-*"
    Range("A1").AutoFilter Field:=2, Criteria1:="<>"
    ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 81

Assisted Solution

by:byundt
byundt earned 250 total points
ID: 40566583
Here are a macro and sample workbook:
Sub DeleteRows()
Dim rg As Range
Dim i As Long, n As Long
Dim s As String
Application.ScreenUpdating = False
With ActiveSheet
    Set rg = .Range("A1").CurrentRegion
End With
n = rg.Rows.Count
For i = n To 1 Step -1
    If rg.Cells(i, 2).Text = "#N/A" Then
        s = UCase(Left(rg.Cells(i, 1).Value, 4))
        If (s = "MAX-") Or (s = "BNC-") Then
            rg.Rows(i).EntireRow.Delete
        End If
    End If
Next
End Sub

Open in new window

I also included Saqib's macro in case you want to test it using the same data. It does give different results.
MatchAndDeleteQ28602538.xlsm
0
 

Author Closing Comment

by:mldaigle1
ID: 40566616
Misters, I have tried both solution and they do work perfectly!

Thanks for your precious help!  :)
0
 

Author Comment

by:mldaigle1
ID: 40566635
May I ask a bit more?

If at the end i would like a MsgBox with the count of how many rows where deleted, how wouild I add it at the macro?
0
 
LVL 81

Expert Comment

by:byundt
ID: 40566645
Modifications to my macro with the MsgBox:
Sub DeleteRows()
Dim rg As Range
Dim i As Long, j As Long, n As Long
Dim s As String
Application.ScreenUpdating = False
With ActiveSheet
    Set rg = .Range("A1").CurrentRegion
End With
n = rg.Rows.Count
For i = n To 1 Step -1
    If rg.Cells(i, 2).Text = "#N/A" Then
        s = UCase(Left(rg.Cells(i, 1).Value, 4))
        If (s = "MAX-") Or (s = "BNC-") Then
            rg.Rows(i).EntireRow.Delete
            j = j + 1
        End If
    End If
Next
MsgBox j & " rows were deleted"
End Sub

Open in new window

0
 

Author Comment

by:mldaigle1
ID: 40566677
byunt,

let's make it a bit harder.....

so the value should be "MAX-" or "BNC-", but not "BNC-CSC" or "BNC-PFM", it this possible?
0
 
LVL 81

Expert Comment

by:byundt
ID: 40566691
Macro modified to not delete rows where column A starts with BNC-CSC or BNC-PFM
Sub DeleteRows()
Dim rg As Range
Dim i As Long, j As Long, n As Long
Dim s As String
Application.ScreenUpdating = False
With ActiveSheet
    Set rg = .Range("A1").CurrentRegion
End With
n = rg.Rows.Count
For i = n To 1 Step -1
    If rg.Cells(i, 2).Text = "#N/A" Then
        s = UCase(Left(rg.Cells(i, 1).Value, 4))
        If (s = "MAX-") Or (s = "BNC-") Then
            s = UCase(Left(rg.Cells(i, 1).Value, 7))
            If (s <> "BNC-CSC") And (s <> "BNC-PFM") Then
                rg.Rows(i).EntireRow.Delete
                j = j + 1
            End If
        End If
    End If
Next
MsgBox j & " rows were deleted"
End Sub

Open in new window

0
 

Author Comment

by:mldaigle1
ID: 40566719
you're wonderful byundt!!

Big kiss!!

that's it for this question, i will probably post another question this afternoon for another macro...!! :)
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question