Excel VBA Ignore Hidden Row and Mass Update from Cells Range (Update Only for Filtered Cell in Excel)

Question

Basically, for now below code are able to mass update my database (SQL Statement update in function GetUpdateTextSQL) from data in range A16 in my worksheet and to check till down everytime there are contents in cells, however what im trying to do is to update only data from cells that are only selected if users filter using excel (Alt+A+T) and ignore hidden cells.

I want the program to only update the cells that have been filtered out by users selection and ignore hidden cells

Private Sub CommandSave_Click()

If MsgBox("All records will be updated. Please make sure that all records all correct!  " & _
          "Continue Saving?", vbYesNo) = vbNo Then Exit Sub

If WorksheetFunction.CountA(Range("B16:K5000")) = 0 Then
    MsgBox "No Records to be Saved"
Else
    Dim cnt As New ADODB.Connection
    Dim CmdForSave As New ADODB.Command
    Dim r As Range
    Dim ConnectionString As String

    ConnectionString = "Provider=SQLNCLI11;Server=ID222222\SQLEXPRESS;Database=Demo;Trusted_Connection=yes;"

    'Opens connection to the database
    cnt.ConnectionTimeout = 30
    cnt.Open ConnectionString

    CmdForSave.ActiveConnection = cnt

    For Each r In Range("A16", Range("A16").End(xlDown))

    CmdForSave.CommandText = _
        GetUpdateTextSQL( _
            r.Offset(0, 1).value, r.Offset(0, 2).value, _
            r.Offset(0, 3).value, _
            r.Offset(0, 4).value, r.Offset(0, 5).value, _
            r.Offset(0, 6).value, _
            r.Offset(0, 0).value)
        CmdForSave.Execute
    Next r

    MsgBox "Data Updated Successfully"

    cnt.Close
    Set cnt = Nothing

End If
End Sub

Tim Williams Tim Williams · Accepted Answer · 2017-07-20T05:26:13

Something like this

'....
For Each r In Range("A16", Range("A16").End(xlDown))
If Not r.EntireRow.Hidden Then
    CmdForSave.CommandText = _
        GetUpdateTextSQL( _
            r.Offset(0, 1).value, r.Offset(0, 2).value, _
            r.Offset(0, 3).value, _
            r.Offset(0, 4).value, r.Offset(0, 5).value, _
            r.Offset(0, 6).value, _
            r.Offset(0, 0).value)
        CmdForSave.Execute
End If
Next r
'....

Excel VBA Ignore Hidden Row and Mass Update from Cells Range (Update Only for Filtered Cell in Excel)

1 Answers