referencing a cell in other worksheet. DYNAMIC/VARIABLE

Question

i have got a question about referencing a cell in other worksheet. I have got this code in VBA:

If Application.CountIf(.Rows(Lrow), '8000')= 0 And Application.CountIf(.Rows(Lrow), '9000')) = 0 Then .Rows(Lrow).Delete

Which is capable for me to delete any row WITHOUT words 8000 and 9000. However, since there would be future update, how can i adjust the code so that the value for excel to execute can be "DYNAMIC" (i.e. not hard-cored) Say, if i enter a number at cell (17,2) in SHEET 1, what can i make excel to look at the cell address instead of the "absolute value" from SHEET 2 by VBA?

Thanks.

Answer 1

You could add these couple of lines above the code you already have. This is assuming that you want to change either number by entering them into cell B17 or B18.

Dim lowerBound as Long, upperBound as Long
lowerBound = Sheet1.Range("B17").Value
upperBound = Sheet1.Range("B18").Value

Then replace '8000' in with lowerBound and '9000' with upperBound.

Answer 2

You can add a column X to your source sheet, headed "Condx for DELETE" and fill this column with a formula representing the DELETE condition as a Boolean value - like in your case: =NOT(OR(A2=8000,A2=9000)) (asuming values 8000, 9000, etc. appear in column A)

Then you cycle thru the source table and delete all records for which column X is TRUE. Asuming that

• your source has been defined within VBA as a range object (MyRange)
• the first row is the header row
• no empty cells in first column between header and end of list
• the condition is found in 3rd column

a purger could look like this

Sub MySub()
Dim MyRange As Range, Idx As Integer, MyCondx As Integer

    Set MyRange = ActiveSheet.[A1]      ' represent source data as a range
    Idx = 2                             ' asuming one header row
    MyCondx = 3                         ' asuming condition is in 3rd column

    Do While MyRange(Idx, 1) <> ""
        If MyRange(Idx, MyCondx) Then
            MyRange(Idx, 3).EntireRow.Delete
        Else
            Idx = Idx + 1
        End If
    Loop
End Sub

This way you have full transparency, no hardcoding and you are very flexible to not only change a set of 2 values but specify whatever condition serves the business.

Hope that helps - good luck