VBA variable error

[rob_grimshaw]

Dear all,

I've written some VBA code to

1. find a value in another spreadsheet and
2. where that value occurs delete the row in the original

However, I keep getting an "Object variable or With block variable not set".
Can you help?

Sub DeleteRows()


    Dim myRange1, v, f, a
    Set myRange1 = Columns("a:a")
    
    For Each v In Workbooks("!Exclusion list.xlsx").Worksheets("Clinic codes").ListObjects("Exclusions").ListColumns(1).DataBodyRange
        Set f = myRange1.Find(what:=v, lookat:=xlPart)
        If Not f Is Nothing Then
            a = f.Address
            Do
                f.EntireRow.Delete
                Set f = myRange1.FindNext
            Loop While f.Address <> a
        End If
    Next


End Sub

 

[Norie]

Rob

If you are deleting the entire row f is in then you are deleting f itself, so when you get here f is Nothing.

Loop While f.Address <> a

What is it you are trying to delete?

 

[Wanderer_geologist]

A loop will be an incremental so an error as pointed will always occure when you try to delete a row
Now, as pointed, the type of record to compare will dictate the check to run, if it's a string, the if myRef=MyComparison should be changed to
strcomp(myref,mycomparison,vbtextcompare)=0

dim WsRe, wsSource as worksheet
dim HeaderRow as integral
dim x, y as long


set wsRe= worksheets("MyChoice")
set wsSource=worksheets("Whatever")
Dim re_count As Long: re_count = wsRe.Cells(Rows.Count, "E").End(xlUp).Row
Dim So_count As Long: So_count = wsSource.Cells(Rows.Count, "E").End(xlUp).Row
dim MyRef, MyComparison as variant




for y=1 to so_count
MyRef= WsSource.cells(y,1).value ' Assuming that the reference to check is in column ADODB.Connection


	For x = 1 To re_count


	RowScan = (re_count + 1) - x
			MyComparison= wsRe.cells(RowScan,1).value
			
			if Mref=Mycomparison then
		
				WsRe.Rows(RowScan).Select
				Selection.Delete Shift:=xlUp
			Else
			'nothing
			End If


	Next x


next y

 

[rob_grimshaw]

Rob

If you are deleting the entire row f is in then you are deleting f itself, so when you get here f is Nothing.

Loop While f.Address <> a

What is it you are trying to delete?

I have a dataset in column A. I want the VBA to compare this to a predefined list contained in another spreadsheet (exclusion criteria). If the data in column A is present in the exclusion criteria then delete the row.

 

[rob_grimshaw]

Could an option be to colour each cell yellow (for example) and then with a different line of code delete every yellow cell?

 

[Norie]

What you could do is put the list into an array then loop, from the bottom, through column A, check if the value in A is in the list and if it is delete the entire row.

Something like this, but obviously you would replace the hard-coded list with your predefined list.

Dim cl As Range
Dim I As Long
Dim arrExclude As Variant
Dim Res As Variant
arrExclude = Array("a", "b", "c")

    For I = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        Set cl = Range("A" & I)
        Res = Application.Match(cl.Value, arrExclude,0)
        If Not IsError(Res) Then
            cl.EntireRow.Delete
        End If
    Next I