VBA for tables

[mib1019]

I know this is simple. I appreciate your time to help me!

I have a table listobject called "TimeData".

Need VBA to insert row above the current row. And VBA to delete the current row.

Going to put both on buttons above the table. Need the VBA to test that the row is within the range between the header and the total row.

Thanks!
MB

 

[bobsan42]

something like this maybe:

Sub listRowClear()
    Dim sh As Worksheet
    Dim obj As ListObject
    Dim ac As Range
    Dim i As Long
   
    Set sh = ActiveSheet
    Set ac = ActiveCell
    Set obj = sh.ListObjects("TimeData")
   
    With obj
        If Intersect(ac, .DataBodyRange) Is Nothing Then GoTo ep
        i = ac.Row - .HeaderRowRange.Row
        .ListRows(i).Delete
        .ListRows.Add i, True
    End With

ep:
    On Error Resume Next
    Set sh = Nothing
    Set obj = Nothing
    Set ac = Nothing
End Sub

I put both of your requirements in one code. Leave this line .ListRows(i).Delete to delete the active row, ot this to insert one: .ListRows.Add i, True

 

[NoSparks]

Here's a couple of links to sites dealing with tables that will help you to understand the parts of tables.

Excel: Working with Tables (VBA)

jkp-ads.com: Excel Tables (VBA)

www.jkp-ads.com

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

 

[mib1019]

Thank you very much!

 

[mib1019]

One issue...if the table is filtered, the insert code doesn't work. 'Runtime error 1004-Can't move cells in a filtered range or table.'

 

[bobsan42]

Well it really can't.
How do you want to handele this situation - ignore it, get a warning of remove the filtering?

 

[mib1019]

Thanks for your help. I added a test for filter and turned it off if necessary. I'm all set!

 

[mib1019]

Bobsan42, I want to get User OK to delete the table row. Please tell me why if he answers No to the message box, it still deletes the row. Thanks!
Debug is telling me that Response is True regardless of which button clicked. Driving me crazy!

Sub Delete_Current_Row()

    Dim Response As Boolean
    
    Response = MsgBox("Delete this row?", vbYesNo + vbQuestion + vbDefaultButton2, "Delete Input Row")
    Debug.Print Response
    
    If Response = vbYes Then
        Dim sh As Worksheet
        Dim obj As ListObject
        Dim ac As Range
        Dim i As Long
   
        Set sh = ActiveSheet
        Set ac = ActiveCell
        Set obj = sh.ListObjects("TimeData")
        With obj
            If Intersect(ac, .DataBodyRange) Is Nothing Then GoTo ep
                i = ac.Row - .HeaderRowRange.Row
                .ListRows(i).Delete
        End With
    Else 'vbNo
        Exit Sub
    End If
    
ep:
    On Error Resume Next
    Set sh = Nothing
    Set obj = Nothing
    Set ac = Nothing
     
    
End Sub

Thanks!

 

[bobsan42]

You have defined response as boolean.
Leave it as variant or msgbox.... Something.
Vbno is not false or 0 so in converts to boolean as True.
So simplest is:

Dim Response

A tip: to see the value of vbNo type ?vbNo in the immediate window and press enter.

 

[mib1019]

DUH. I knew that. But my mind is a mystery! Thanks!