VBA mod

[dantheram]

Hi all,

can someone help me to mod this so that when the rows identified are moved to another sheet the subsequent blank row is deleted from the source table, please?

Sub Move_Closed()Dim Check As Range, r As Long, lastrow2 As Long, lastrow As Long
Application.ScreenUpdating = False
lastrow = Worksheets("Open").UsedRange.Rows.Count
lastrow2 = Worksheets("Completed").UsedRange.Rows.Count
If lastrow2 = 1 Then lastrow2 = 0
    For r = lastrow To 2 Step -1
        If Range("E" & r).Value = "Y" Then
            Rows(r).Cut Destination:=Worksheets("Completed").Range("A" & lastrow2 + 1)
            lastrow2 = lastrow2 + 1
            Else:
        End If
    Next r
Application.ScreenUpdating = True
End Sub

 

[dantheram]

Is your data in a structured table?

yep

 

[Fluff]

In that case try

    If Not Rng Is Nothing Then Intersect(Rng, Sheets("Data").ListObjects([COLOR=#ff0000]1[/COLOR]).DataBodyRange).Delete

Change the value in red if needed.

 

[dantheram]

In that case try

    If Not Rng Is Nothing Then Intersect(Rng, Sheets("Data").ListObjects([COLOR=#ff0000]1[/COLOR]).DataBodyRange).Delete

Change the value in red if needed.

is the ListObjects(1) component referring to the table name within the xls? If so, does it need to be as follows - ListObjects("Table13")?

 

[Fluff]

No it's referring to the number, if you only have 1 table on the sheet then leave it as-is.

 

[dantheram]

No it's referring to the number, if you only have 1 table on the sheet then leave it as-is.

Spotted it - sheet name is wrong in my VBA, will try again

Dan

 

[dantheram]

No it's referring to the number, if you only have 1 table on the sheet then leave it as-is.

its working fine when i adjust the ListObjects(#) now. However, i have 2 tables on the sheet which i require it to act upon, so ListObjects(1) and ListObjects(2), how do i repeat the function for both?

 

[Fluff]

You'll need to change the range it loops through & the listobject number to match the other table