Who can help/analyse? tried all kinds of statements but rows are not added/deleted.

Hoekstein, Arnold 1 Reputation point
2020-06-22T10:25:48.837+00:00

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

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

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,594 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Hoekstein, Arnold 1 Reputation point
    2020-06-22T10:29:00.083+00:00

    Title should contain VBA:
    VBA deleting/inserting rows , no errors, no result

    0 comments No comments

  2. Leon Laude 85,716 Reputation points
    2020-06-22T10:29:01.843+00:00

    Hi,

    VBA is currently not supported in the Q&A forums, the supported products are listed over here https://learn.microsoft.com/en-us/answers/products (more to be added later on).

    You can ask the experts in the dedicated VBA forum over here:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=vbgeneral

    (Please don't forget to accept helpful replies as answer)

    Best regards,
    Leon


  3. Hoekstein, Arnold 1 Reputation point
    2020-06-22T10:33:09.18+00:00

    Site no longer supported, says:

    The following forum(s) have migrated to Microsoft Q&A:All English Microsoft Azure forums, Developing Universal Windows apps, Microsoft Partner Center API, Windows Presentation Foundation (WPF), Windows 10 Installation, Setup, and Deployment, Active Directory Federation Services!
    Visit Microsoft Q&A to post new questions.