vba programming - delete entire row, not containing

Question

I am new to macros and VBA, so this might be easy.

I need to filter a list of companies, such that companies not matching branchcode are removed from the excel-tab.

More precisely:

company.xls contains the full list of companies, where column N, is containing branchcodes
branch.xls, column A contains the branchcodes of relevance, which has to be used to filter the comapnies in company.xls
The companies in company.xls that do not have a matching
branchcode should be removed from tab1 to tab2 of company.xls.

I hope it make sense!?

Thanks in advance for your responses.

Function Search_String(x As String) As Boolean
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim Contained As Boolean
Contained = True

'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With Sheets("codes")

    'We select the sheet so we can change the window view
    .Select

    'Set the first and last row to loop through
    Firstrow = .UsedRange.Cells(1).Row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

    'We loop from Lastrow to Firstrow (bottom to top)
    For Lrow = Lastrow To Firstrow Step -1

        'We check the values in the A column in this example
        With .Cells(Lrow, "A") 'Column letter for codes sheet

            If Not IsError(.Value) Then
                If InStr(x, .Value) Then Contained = False
            End If
        End With
    Next Lrow
End With

Search_String = Contained
End Function


Sub Filtrer()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

With Sheets("search (14)") 'Sheet name with rows to be deleted
    'We select the sheet so we can change the window view
    .Select

    'If you are in Page Break Preview Or Page Layout view go
    'back to normal view, we do this for speed
    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView

    'Turn off Page Breaks, we do this for speed
    .DisplayPageBreaks = False

    'Set the first and last row to loop through
    Firstrow = .UsedRange.Cells(1).Row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

    'We loop from Lastrow to Firstrow (bottom to top)
    For Lrow = Lastrow To Firstrow Step -1

        With .Cells(Lrow, "N") 'Change this to the correct Sheets column that needs deleting
            If Not IsError(.Value) Then
                If Search_String(.Value) Then .EntireRow.Delete
            End If
        End With
    Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
    .ScreenUpdating = True
    .Calculation = CalcMode
End With

End Sub

Can you show what code you have already written? I'm happy to help you debug your code and answer questions about how to reference Workbooks, Worksheets and Ranges in your code. However if all you want is "can you write code for me?" the answer is no. If I was going to solve this problem, I'd bring both data sets into Access. Solving this problem with a query would take 10 minutes max. — Blackhawk
– Blackhawk, Commented Sep 26, 2014 at 15:57
Thanks for replying. I have some code. But how do I post it here? Max characters is exceeded. — user3697608
– user3697608, Commented Sep 29, 2014 at 7:21
Instead of pasting it into a comment, you can click the "edit" link underneath your question and add the code there. There should be plenty of room, but really all you need to do is a minimum example of what you're doing. For example, are you using the AutoFilter functionality? Are you looping through each row to check it against the list of companies? Are you loading the list into a data structure like an an array or Collection? If you include a small snippet of your code that answers those kinds of questions, it should be enough. — Blackhawk
– Blackhawk, Commented Sep 29, 2014 at 13:49
Thanks again for responding. I pasted the entire code. I actually made it work, so in fact my question now, is regarding this line of code With Sheets("search (14)" . The thing is, that the macro will be used on a number in a number of different workbooks, which is why the aforementioned code needs to change every time, depending on the name of the sheet. So I was wondering if it is possible to code some sort of dialog box, which asks the user of the macro, to type the name of the worksheet before running the macro? The same property would be nice for this line of code With .Cells(Lrow, "N") — user3697608
– user3697608, Commented Sep 30, 2014 at 7:06

Sathish Kothandam · Accepted Answer · 2014-09-30 07:43:06Z

1

Correct me if i'm wrong .Now you want to let the user input the sheetname and columnName

Then the following code will help you .

Dim SheetName As String
Dim ColumnName As String

SheetName = InputBox("Enter the Sheet Name ?") ' Ex Sheet1
ColumnName = InputBox("Enter the column Name ?") ' Ex N

With Sheets(SheetName) ' Replace this line
With .Cells(Lrow, ColumnName) ' Replace this line

answered Sep 30, 2014 at 7:43

Sathish Kothandam

1,5203 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3697608 Over a year ago

Thanks Sathish. It was exactly what I wanted, and it works great! Thanks again

user3697608 Over a year ago

I'd love to accept it, however, as I am "new here" I can't before reaching 15 reputation... If I understand correctly?

Collectives™ on Stack Overflow

vba programming - delete entire row, not containing

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related