Adding control box to VBA

**3345james** · 06-14-2016, 06:01 PM

I'd like to make this macro interactive by adding a box to select column and text with wildcards
Currently I change the "F" and the "*:*" in the code below and then run the macro. But, I'd like to make it interactive so others with no experience with vba can do it.

Any help greatly appreciated.

Jim

rows in macro that are affected
With .Cells(Lrow,"F") ‘insert column
If Not IsError(.Value) Then
If .Value Like "*:*" Then .E. . . .

Sub Delete_Row_IfCellContainsCertainText ()
    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 ActiveSheet
        .Select

        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
        .DisplayPageBreaks = False

        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        For Lrow = Lastrow To Firstrow Step -1
            With .Cells(Lrow,"F") ‘insert column
                If Not IsError(.Value) Then
                    If .Value Like "*:*" Then .EntireRow.Delete ‘insert common text with wildcard
                End If
            End With
        Next Lrow
    End With
 
    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub

**MarvinP** · 06-14-2016, 10:14 PM

Hi Jim,

How about using an InputBox and ask what column Letter to look in for stuff to delete? Save this variable and use in place of your "F" in your code.

**3345james** · 06-15-2016, 04:51 AM

Thanks for the response Marvin
I'm not great with VBA - I try to find working code to our applications and then string codes together. I've not created an Input box for this type of application. Could you help me out with the how.
Input Column & Input text to look for (if text found delete row)

Thanks again

**MarvinP** · 06-15-2016, 08:12 AM

Try this

Sub Delete_Row_IfCellContainsCertainText()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim ColumnLetter As String

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

        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
        .DisplayPageBreaks = False

        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        ColumnLetter = InputBox("What Column do you want to look in?")
        For Lrow = Lastrow To Firstrow Step -1
            With .Cells(Lrow, ColumnLetter)
                If Not IsError(.Value) Then
                    If .Value Like "*:*" Then .EntireRow.Delete
                End If
            End With
        Next Lrow
    End With

**3345james** · 06-15-2016, 08:46 AM

Thanks Marvin - this part works great but need input box to declare this line
If .Value Like "*:*" Then .EntireRow.Delete

The *:* could be anything desired - so how could I incorporate that query?

**MarvinP** · 06-15-2016, 09:20 AM

Same idea

Sub Delete_Row_IfCellContainsCertainText()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim ColumnLetter As String
    Dim LookFor As String

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

        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
        .DisplayPageBreaks = False

        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        ColumnLetter = InputBox("What Column do you want to look in?")
        LookFor = InputBox("What do you want to delete?")
        For Lrow = Lastrow To Firstrow Step -1
            With .Cells(Lrow, ColumnLetter)
                If Not IsError(.Value) Then
                    If .Value Like "*" & LookFor & "*" Then .EntireRow.Delete
                End If
            End With
        Next Lrow
    End With
End Sub

**3345james** · 06-15-2016, 09:47 AM

This is sweat Marvin - works like a charm in so many ways. Can't thank you enough
Jim

Adding control box to VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Adding control box to VBA

Re: Adding control box to VBA

Re: Adding control box to VBA

Re: Adding control box to VBA

Re: Adding control box to VBA

Re: Adding control box to VBA

Re: Adding control box to VBA

Thread Information

Users Browsing this Thread

Similar Threads

adding a vba search code to a button control

Adding Form Control Buttons

Adding a Control Caption

Adding Control to a Dynamic Listbox

Adding the VB Timer Control...

Adding a control to a User Form

[SOLVED] Adding Button to Popup Control

Adding a Control programatically

Bookmarks

Bookmarks

Posting Permissions