Finding the value to plug into the ( ) - please assist

[kingleviathan]

Hi Guys

i have this code -

range("A" & ?).select
Selection.ListObject.ListRows(?).Delete

I need to use this code as entirerow.delete or similar code will not work

In column AA - starting at cell 1 - i have cells with numbers ie AA1 has 3 AA2 has 5, AA3 has 20

So starting with the laste used cell (ie. in my example it is AA3) - I need 20 minus 1 so I need 19 inserted into the (?)

The process is then repeated for AA2. So AA2 has 5 -1 (4) inserted into the (?) in my code.

Ditto for AA1

Note: it is very possible that in some cases there may be no entries in AA..
Also - It is unknown what will be the cell used in AA a row count for last cell in AA may be required.

Can anyone assist?

 

[kingleviathan]

This was my attempt but I'm sure it can be done better

Sub Test()
 
Dim r as range
Dim LastRow as long
 
Do while ("AA1") <> ""                                                 'if first cell is blank then
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p>          LastRow = Cells(Rows.Count, "AA").End(xlUp).Row  'Find last cell in AA</o:p>
          set r = cells.value("AA" & LastRow)                      'make r the value of last cell in AA
<o:p></o:p>
          range("A" & r).select                                         'Select cell of row to be deleted in A (not AA)                                          
          Selection.ListObject.ListRows(r).Delete                 'Delete that row 
 
          r.delete                                                          'Delete last cell used in AA
 
loop
 
End sub

 

[kingleviathan]

but my code doesnt work ...Some type mis-match 13.

If anyone can assist that would be awesome...

Thanks for your help