Solved

Excel Macro to find computer name start by and delete row

Posted on 2015-01-23
10
144 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
 
LVL 80

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 80

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 80

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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 Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now