Solved

Excel Macro to find computer name start by and delete row

Posted on 2015-01-23
10
160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

696 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