[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel Macro to find computer name start by and delete row

Posted on 2015-01-23
10
Medium Priority
?
168 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 1000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 81

Assisted Solution

by:byundt
byundt earned 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

649 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