1

I have looked for this everywhere but can't find an example of it. I have a code which searches a column and deletes all rows that contain the exact value within that cell.

I want to make this a code that could be used in any sheet with two input boxes the first asking the user which column to look in(this is what i am having an issue with)

and the second asking for the criteria.

The existing code is like this.

    Sub OCR()
    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

   'Amend test
    With .Cells(Lrow, "B")
    If Not IsError(.Value) Then
    If .Value = "Test" Then .EntireRow.Delete
    End If
    End With


    'END of loop
        Next Lrow

    End With

So This will delete all rows where Test is in the relevant column (B), but how can I use an InputBox to decide the column and the value to look for.

Community
  • 1
  • 1
Flaunting
  • 168
  • 7
  • I suggest to let the user enter column name and criterium into 2 cells on the Excel sheet and read the values from your VBA code. Otherwise you need two input boxes, one for column name, one for criterium, at the begin of your code (after Dim maybe) – MikeD Sep 02 '14 at 10:25
  • But this won't be applied to just one spreadsheet. so I can't gaurentee that those cells will always be available – Flaunting Sep 02 '14 at 10:26
  • That is just not the right way to achieve what you want :) Use `AUTOFILTER`. See [THIS](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) and after the filter simply delete the rows. – Siddharth Rout Sep 02 '14 at 10:42
  • You can use `Application.InputBox` with `Type 8` to let the user select a range. and `Type 2` for accepting text. – Siddharth Rout Sep 02 '14 at 10:44
  • @SiddharthRout Can i ask what is wrong with the method that i use? – Flaunting Sep 02 '14 at 12:21
  • Sure :) `1` Looping is way much slower as compared to "Autofilter" when you are dealing with large number of rows :) – Siddharth Rout Sep 02 '14 at 12:42
  • I am only dealing with a maximum of 500 rows, and it works instantly for me so i am fine with it atm, I want to keep to this method as it is what i have implented in a few others, but will keep your suggestion in mind if i deal with anything larger. – Flaunting Sep 02 '14 at 12:50

1 Answers1

2

Replace:

With .Cells(Lrow, "B")

with:

s = Application.InputBox("Pick a cell", Type:=8).Address(1, 0)
lettre = Split(s, "$")(0)
With .Cells(Lrow, lettre)

EDIT#1

When the InputBox appears, use the mouse to pick any cell in the desired column.

EDIT#2

When the InputBox appears, type the cell address to pick any cell in the desired column. Your ScreenUpdating setting prohibits using the mouse.

EDIT#3

The final approach used by Flaunting was:

s = Application.InputBox("Enter Column Data", Type:=2)
With .Cells(Lrow, s)
Gary's Student
  • 95,722
  • 10
  • 59
  • 99