For each entirerow in selection of entirerows?

[ClimoC]

My Query Table has a 'catchdelete' (Onkey) sub that determines if the whole row is selected. If it is, it looks up the ID in the table and sends the SQL command to remove the record. If it's not the entirerow, it resumes 'clearcontents'. Simple Right?

Public Sub CaughtDelete()
If cnImportConn Is Nothing Then Run "ActivateConn"

If Selection.Address = ActiveCell.EntireRow.Address Then
            iD = Cells(ActiveCell.Row, 2).Value
            strsql = "DELETE * FROM [work] WHERE ID = " & iD
            cnImportConn.Execute (strsql)
Else
            Selection.ClearContents
End If

End Sub

But I'd like this to work for multiple rows.

As there is no 'selected' boolean property for the 'EntireRow' range object, can anyone think of a way to basically do this?:

If cnImportConn Is Nothing Then Run "ActivateConn"
        If Selection.Rows.Count > 1 Then  'this is fine - a valid statement
                    For Each r In Selection.Rows
                                If Cells(r.Row, 2).EntireRow.Selected Then  'here's the test I need to perform
                                    iD = Cells(r, 2).Value
                                    strsql = "DELETE * FROM [work] WHERE ID = " & iD
                                    cnImportConn.Execute (strsql)
                                Else
                                    Selection.ClearContents
                                End If
                    Next
        End If
End If

 

[Firefly2012]

Perhaps:

If r.Count = Activesheet.Rows(1).Count Then ....

 

[ClimoC]

Hmm, Thanks Firefly - worked for entirerows selected, but it fails to distinguish between a typical range selection (in this case "C30:H42") and the entirerows

 

[ClimoC]

Hmm, Thanks Firefly - worked for entirerows selected, but it fails to distinguish between a typical range selection (in this case "C30:H42") and the entirerows

updated code

Public Sub CaughtDelete()
If cnImportConn Is Nothing Then Run "ActivateConn"

    If Selection.Rows.Count > 1 Then
                    For Each r In Selection.Rows
                                If r.Count = Sheets("WorkFlow").Rows(1).Count Then
                                    iD = Cells(r.Row, 2).Value
                                    strsql = "DELETE * FROM [work] WHERE ID = " & iD
                                    cnImportConn.Execute (strsql)
                                Else
                                    Selection.ClearContents
                                End If
                    Next
    Else
            If Selection.Address = ActiveCell.EntireRow.Address Then
                    iD = Cells(ActiveCell.Row, 2).Value
                    strsql = "DELETE * FROM [work] WHERE ID = " & iD
                    cnImportConn.Execute (strsql)
            Else
                    Selection.ClearContents
            End If
    End If


End Sub

This will delete multiple records from the database, but cannot equate 'entirerow selected is false', therefor can't clearcontents if the range covers more than one row

 

[Firefly2012]

Can you explain what you mean - ie provide an example of what isn't working with code?

 

[ClimoC]

Can you explain what you mean - ie provide an example of what isn't working with code?

Got it. This works. Must've been the second coffee kicking in

Public Sub CaughtDelete()
If cnImportConn Is Nothing Then Run "ActivateConn"

    If Selection.Rows.Count > 1 Then
                    For Each r In Selection.Rows
                                If r.Address = r.EntireRow.Address Then
                                    iD = Cells(r.Row, 2).Value
                                    strsql = "DELETE * FROM [work] WHERE ID = " & iD
                                    cnImportConn.Execute (strsql)
                                Else
                                    Selection.ClearContents
                                End If
                    Next
    Else
            If Selection.Address = ActiveCell.EntireRow.Address Then
                    iD = Cells(ActiveCell.Row, 2).Value
                    strsql = "DELETE * FROM [work] WHERE ID = " & iD
                    cnImportConn.Execute (strsql)
            Else
                    Selection.ClearContents
            End If
    End If


End Sub

The sub needs to check for:

1. If the number of rows in the activeselection is 1, then if the entirerow is selected, delete the record - otherwise clearcontents (which in turn triggers the worksheet change, which will clear the DB values instead of deleting the record

2. If the number of rows in the activeselection is MORE than 1, delete each row (record) from the DB, else clear each DB value

 

[RoryA]

no real need for the test on rows.count

Public Sub CaughtDelete()
    If cnImportConn Is Nothing Then Run "ActivateConn"

    For Each r In Selection.Rows
        If r.Address = r.EntireRow.Address Then
            ID = Cells(r.Row, 2).Value
            strsql = "DELETE * FROM [work] WHERE ID = " & ID
            cnImportConn.Execute (strsql)
        Else
            r.ClearContents
        End If
    Next r
End Sub