Hi there,
I appreciate various forms of this question has been posted many times before but despite weeks of searching I can't find an answer that is quite right for me.
I have a spreadsheet which contains 3 tables. Each table contains the same data but with different sorting criteria applied. The volume of data changes each time the spreadsheet is used so the size of tables also change. Therefore, whilst my first table always starts from the same row, the starting rows for tables 2 and 3 varies. Therefore for any macros applied to tables 2 and 3 I cannot reference specific cells. I have got around this previously by placing my cursor in column A at the start of each table and using active.offset instead.
The bit where I am getting stuck is that I need to delete any rows from table 3 where column L contains the word "Rejected". I have some simple code using a static starting point at the top of my file which works fine but I just can't get it to work using an offset starting point which is what I really need.
So my code using a static starting point is:
[I]Sub Delete_Row2()
For i = Range("L2").End(xlDown).Row To 2 Step -1
If Not IsEmpty(Cells(i, 12)) And Not IsNull(Cells(i, 12)) Then
If Cells(i, 12) = "Rejected" Then
Rows(i).Delete Shift:=xlUp
End If
End If
Next i
End Sub
I have tried changing it as follows but just get an error:
Sub Delete_Row2()
For i = Range(ActiveCell.Offset(0, 11)).End(xlDown).Row To Range(ActiveCell.Offset(0, 11)) Step -1
If Not IsEmpty(Cells(i, 12)) And Not IsNull(Cells(i, 12)) Then
If Cells(i, 12) = "Rejected" Then
Rows(i).Delete Shift:=xlUp
End If
End If
Next i
End Sub
It is worth noting that I only have a very basic knowledge of vba and I didnt actually write this code myself so any help anyone has is most appreciated!!
Thanks in advance
Bookmarks