CODEWITHSUNDEEP
vbaexcel
Simple Poodle
Simple Poodle

Reputation: 7

VBA - how to use macro button for multiple boxes

I am new to VBA and macro. I will try to explain what I am trying to create then I will explain what problem I have with it.

There are two sheets in my Excel; 'Sheet 1' and 'Sheet 2'.

Sheet 1 is full of data. This data goes from column A to AK and there are 4206 rows.

Sheet 2 only consist an input cell box with a 'GO' button next to the box. The button is assigned to a macro.

What do I want to create?

In the input cell box I type something like 'GB' and then press the 'GO' button. The 'GO' button will look through Sheet 1 for cells with 'GB' in them. There are two particular columns that could have 'GB' in them; one of them is column K and one is column L. The 'GO' button will look in those two columns for 'GB' and filter the rows.

Important note: I don't want to design a macro so that they will look for 'GB' in column K and column L. Instead I want them to look for 'GB' in column K or column L.

What did I create?

I designed a macro and assigned it to the 'GO' box. This is the code that I put in:

Option Explicit

Sub Macro1()
'
' Macro1 Macro
'
    Sheets("Sheet 1").Select
    If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
    End If
    Range("A12:AM4216").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("A1:AK2"), Unique:=False

End Sub

What works with the code? And what is the problem?

The code will return rows that has 'GB' in column L. This is great. However, I am looking of ways how to change the code so that it will return rows that has 'GB' in column K or column L.

Any advice on this please?

Thank you.

Upvotes: 0

Views: 301

Answers (1)

barvobot
barvobot

Reputation: 897

I think something like this should probably do it. I was a little confused on whether the 'GB' was something that would vary or if it would be the same every time, so I included the option for both (by default the code assumes it's static). I also didn't know if you wanted it case-sensitive, so by default, it is NOT case sensitive (to make it case-sensitive, remove the LCase functions).

Sub Macro1()
Dim ws As Worksheet
Dim firstRow As Long, lastRow As Long
Dim firstCol As String, secondCol As String
Dim findStr As String
Dim x As Long

'define worksheet to check
Set ws = ActiveWorkbook.Sheets("Sheet1") 'or whichever sheet

'define search string to check for based on static value
findStr = "GB"

'define search string to check for based on cell value
'findStr = ws.cells("A1") 'or whichever cell

'define start and end rows to loop through
firstRow = 12
lastRow = 4216

'define columns to check
firstCol = "K"
secondCol = "L"

'turn off screenupdating
Application.ScreenUpdating = False

'unhide rows in range
ws.Range(ws.Rows(firstRow).EntireRow, ws.Rows(lastRow).EntireRow).Hidden = False

'loop through the rows
For x = firstRow To lastRow
    'if either cell has the search string in it (regardless of case)...
    If InStr(LCase(ws.Range(firstCol & x)), LCase(findStr)) Or InStr(LCase(ws.Range(secondCol & x)), LCase(findStr)) Then
        '...do nothing
    Else
        '...otherwise, hide the row
        ws.Rows(x).EntireRow.Hidden = True
    End If
Next x

'turn screenupdating back on
Application.ScreenUpdating = True    

End Sub

Please note that like Cyril mentioned above, this is not a true filter: it just hides rows that don't match the specified criteria.

Upvotes: 1

Related Questions