ListObject.ListRows question

[ParanoidAndroid]

Hi All

I use the following to delete a row in a table

Range("A" & x).Select
Selection.ListObject.ListRows(x-1).Delete

How do I get this to apply to multiple sheets? I have problem with the ws in following line ws.Range("A" & x).Select. I suppose I could omit that line altogether except i dont know how to define the .listobject ie. .listobject(*)

Sub Del_Row_in_Table
For each ws in sheets(array("sheet1", "sheet2", "sheet3"))
              ws.Range("A3").Select
              ws.Selection.ListObject.ListRows(2).Delete 
next ws
end sub

Your assistance will be greatly appreciated as im stuck grrrr
Note: this is a simplified version of my code

 

[Peter_SSs]

Try a structure like this.

Sub Del_Row_in_Table()
    Dim i As Long
    Dim mySheets
    
    mySheets = Array("sheet1", "sheet2", "sheet3")
    For i = LBound(mySheets) To UBound(mySheets)
        Sheets(mySheets(i)).Range("A3").ListObject.ListRows(2).Delete
    Next i
End Sub

 

[ParanoidAndroid]

Thanks Peter

I tested your code and it works perfectly...So very grateful.

I've tried to adapt it to my code...but the following line seems problematic

Sheets(mySheets(i)).Range(r).ListObject.ListRows(r - 1).Delete

It gives me the message run-time error '1004' application-defined or object defined error

below is my code using your approach - any ideas?

Sub Del_Row_in_Table()
    Dim i As Long
    Dim mySheets
    Dim r As Integer
    Dim LastRow As Integer
 
    mySheets = Array("Sheets1", "Sheets2", "Sheets3")
    For i = LBound(mySheets) To UBound(mySheets)
        If Sheets(mySheets(i)).Range("AA1") <> "" Then
            Do
                LastRow = Sheets(mySheets(i)).Range("AA" & Rows.Count).End(xlUp).Row
                r = Sheets(mySheets(i)).Range("AA" & LastRow).Value
                Sheets(mySheets(i)).Range(r).ListObject.ListRows(r - 1).Delete
                Sheets(mySheets(i)).Range("AA" & LastRow).ClearContents
            Loop Until Sheets(mySheets(i)).Range("AA1") = ""
        End If
    Next i
End Sub

 

[Peter_SSs]

Thanks Peter

I tested your code and it works perfectly...So very grateful.

I've tried to adapt it to my code...but the following line seems problematic

Sheets(mySheets(i))[COLOR="Red"].Range(r)[/COLOR].ListObject.ListRows(r - 1).Delete

It gives me the message run-time error '1004' application-defined or object defined error

You have declared 'r' as an integer, so what do you mean by .Range(r) in this line of code?

 

[ParanoidAndroid]

r is a number (it lists the row number) that is in column AA

So what my code does is...

Looks for the last used cell in column AA then deleted that row number in the table

deletes the last used cell in AA

and repeats the process until there are no more used cells in column AA.

So for example AA1 might have 3 (ie row number 3), AA2 might have 5, AA3 might have 7.

So my code finds AA3 and deletes row 7 in the table then deletes AA3, then repeats for AA2 and AA1 until AA1 is empty...

Note: AA cells will always be a number. unlikely to be more than 3 digits (but you never know - ive never seen it greater than 999).
and It is highly unlikely to be more than 10 cells used in AA ie any more than AA10 - more likely 5 cells in AA used at most - but again you never know. 20 max i would say

Also i should add...AA is outside the table area....

 

[Peter_SSs]

I haven't tried to follow that in detail yet but the point I was trying to make was. My original line of code (that worked) was

Sheets(mySheets(i)).Range("A3").ListObject.ListRows(2).Delete

The "A3" is a range address so vba understands Range("A3") as a range.

In your code, if r = 3 say
'3' is not a range address and so vba does not interpret Range(3) as a range.

If your table was in column A then you would need something like this instead

Sheets(mySheets(i)).Range("A" & r).ListObject.ListRows(r - 1).Delete

 

[ParanoidAndroid]

Thats ok Peter...

you are correct. I have fixed that up and it works an absolute treat.

I am very grateful for your help and time. It is much apprecaited

Fantastic.