Dynamic Range Help!

[tovaibhav]

I have given a dyanmic range in my worksheets as
=OFFSET(Configuration!$C$2,0,0,COUNTA(Configuration!$C:$C)-1,1)

Configuration: Name of the sheet
I am starting range from 2nd row and the column is 3rd.


Problem:
Now I match value of combobox to values of Column D in the same sheet.
It it matches I delete the entire row.
Here is the code.

Dim Flag As Integer
Flag = 2

For x = 2 To 100 'As my rows will be less than 100
ThisValue = Me.ComboBox3.Value
If ThisValue = ws.Range("D" & x) Then
ws.Rows(x).Delete
End If
Next x

Problem when the match is for the Second row(my dynamic ranges starting from second row) and gets deleted than my dyanmic range becomes
=OFFSET(Configuration!#REF!,0,0,COUNTA(Configuration!$C:$C)-1,1)

Which is effecting my code.
Please help

 

[Norie]

Where does the dynamic named range come into it?

Is that the range you want to delete from and/or use for to decide if a row should be deleted?

By the way, why not create the range in the code?

Or do you need the named range for other purposes?

Do you even need it at all? If all you want to do is loop through all the cells with data in column C you could use something like this.

Dim rng As Range
Dim LastRow As Long
Dim I As Long
 
    LastRow = Worksheets("Configuration").Range("C"& Rows.Count).End(xlUp).Row
 
    For I = LastRow to I Step -1
        
       Set rng = Worksheets("Configuration").Range("D" & I)
 
       ThisValue = Me.ComboBox3.Value

        If ThisValue = rng.Value Then
            rng.EntireRow.Delete
        End If
 
   Next I

Oh and if you are deleting you should loop backwards.

 

[tovaibhav]

Thanks for making deleting code
Actually Problem arises when i delete the 2nd row in a sheet.

As in my sheet there is a named range as
=OFFSET(Configuration!$C$2,0,0,COUNTA(Configuration!$C:$C)-1,1)

after deleting 2nd row it becomes
=OFFSET(Configuration!#REF!,0,0,COUNTA(Configuration!$C:$C)-1,1)

 

[Norie]

If you are deleting C2,with code or manually, then that's going to happen.

I don't know of anyway round it apart from (re)creating the dynamic named range in the code after the deleting.

Dim rngNamed As Range
 
      LastRow = Worksheets("Configuration").Range("C" & Rows.Count).End(xlUp).Row
 
      Set rngNamed = Worksheets("Configutation").Range("C2:C"& LastRow)
 
      rngNamed.Name = "TheNameForTheRange"

PS I think there is a way to do it without code, using INDIRECT.

I'm afraid formulas aren't really my thing though.

 

[tovaibhav]

Thanks for the help and also for a fast response