Solved

Excel Macro to find computer name start by and delete row

Posted on 2015-01-23
10
145 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 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

911 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

22 Experts available now in Live!

Get 1:1 Help Now