0

Who can help/analyse? tried all kinds of statements but rows are not added/deleted. Below is not the most effective way in VBA but I had to find out why statements do not process without giving errors. I am using tables (listobjects) so I do not have to use cell/row adresses that use column-letters or row-number.

(Have excell-sheet available, do not know how to attach here)

Function TableResizeInRows(SheetName As String, TableName As String, NumberDataRows As Integer) As String
'NO ERROR BUT ALSO NO RESULT
Dim l_Tableref As ListObject
Dim l_SheetName As Worksheet
Dim l_range As Range
Dim l_ListRows As ListRows
Dim MyNewRow As ListRow
Dim c_ListRows As Long
Dim InsertIndex As Integer
Dim DeleteIndex As Integer
Dim HeaderIsOnRow As Integer
Dim StartWsRow As Integer
Dim EndWsRow As Integer
On Error GoTo ErrorHandler

If (NumberDataRows < 2) Then
    TableResizeInRows = "Row 1 not to be deleted, formulas will be deleted too!"
    Exit Function
End If

Set l_SheetName = Worksheets(SheetName)
' make goal-worksheet active (set correct context)
l_SheetName.Activate
' Define Table Object
Set l_Tableref = Sheets(SheetName).ListObjects(TableName)
' determine # rows in listobject
Set l_ListRows = l_Tableref.ListRows
c_ListRows = l_Tableref.ListRows.Count
' determine rownumber on which the table is
HeaderIsOnRow = Sheets(SheetName).ListObjects(TableName).HeaderRowRange.Row
' Now we have sheet-rows that should be deleted/inserted
If (c_ListRows > NumberDataRows) Then
                ' We have to delete rows but it should be (the last row) of the listobject (Table)
                ' EndWsRow = last row that is to be deleted (lowest row#)
    EndWsRow = (HeaderIsOnRow + NumberDataRows) + 1
                ' StartWsRow = First row that is to be deleted (highest row#)
    StartWsRow = HeaderIsOnRow + c_ListRows
    For DeleteIndex = StartWsRow To EndWsRow Step -1
'NO ERROR BUT ALSO NO RESULT
'        Set l_range = l_SheetName.Range("A" & DeleteIndex)
'        Range("A" & DeleteIndex).EntireRow.Delete
        Worksheets(SheetName).Rows(DeleteIndex).Delete
        'l_Tableref.ListRows(DeleteIndex - HeaderIsOnRow).Delete 'Based on row in table 'results in error 1004
    Next
    TableResizeInRows = "Success, rows deleted?"
    Exit Function
Else
    If (c_ListRows < NumberDataRows) Then
                ' We must insert directly below the listobject (Table)
                ' StartWsRow = Last row of table after which a row is to be inserted(so last rownumber on sheet that contains tablerow)
        StartWsRow = HeaderIsOnRow + c_ListRows
                ' EndWsRow = Last row after which one more row is to be inserted
        EndWsRow = (HeaderIsOnRow + NumberDataRows) - 1
        For InsertIndex = StartWsRow To EndWsRow Step 1
'NO ERROR BUT ALSO NO RESULT
            Worksheets(SheetName).Rows(InsertIndex).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Next
        TableResizeInRows = "Success, rows inserted?"
        Exit Function
    End If
End If
TableResizeInRows = "Result unknown"
Exit Function

ErrorExit:
    On Error Resume Next
    Exit Function

ErrorHandler:
' Public Function bCentralErrorHandler
' see https://stackoverflow.com/questions/19042604/vba-excel-error-handling-especially-in-functions-professional-excel-developm
    If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If
End Function
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
ArnoldHatCGI
  • 1
  • Get rid of all the error handling right now and then figure out *where* the error is actually occurring. – BigBen Jun 22 '20 at 14:27
  • How are you calling this? – Scott Craner Jun 22 '20 at 14:28
  • `snake_case` just looks **so** wrong here btw. – BigBen Jun 22 '20 at 14:28
  • Sorry, removing the line On Error GoTo ErrorHandler does not make any difference, It does not give any error, that's the issue. – ArnoldHatCGI Jun 22 '20 at 14:29
  • 3
    This will not work being called from the worksheet. A function called as a formula from the worksheet(UDF) will not make changes to anything but the value of the cell from which it is called. It will not format or delete or change the value of any other cell. – Scott Craner Jun 22 '20 at 14:32
  • Thanks for this, this can help me further. I am relatively new, in VBA. Does this mean that it should be a subroutine or isn't is possible from within excel? – ArnoldHatCGI Jun 22 '20 at 14:36
  • It is possible as a sub, but the sub will need to be triggered some how. One can use worksheet events, keyboard shortcuts or buttons to trigger the subroutine. – Scott Craner Jun 22 '20 at 14:40
  • Thanks, you were very helpfull, now I know the issue I will find a way around. Being a newbee, how I close this question? – ArnoldHatCGI Jun 22 '20 at 14:42
  • 1
    @ArnoldHatCGI. You can delete your own question. Should be some options in grey right under the question. [share edit delete etc] – Scott Holtzman Jun 22 '20 at 14:57

1 Answers1

0

Answer is in the comments, wrong way of calling the function, not possible from within a cell. Thanks to Scott Craner

ArnoldHatCGI
  • 1