Excel Delete Command does not change the last line

Mackeral

Board Regular
Joined
Mar 7, 2015
Messages
70
This is my code:S
VBA Code:
Sub Data_Delete_Rows(Sheet_Spec, _
                             Row_Start, _
                             Optional Row_End = -1)
    ' Delete indicated Rows & returning Start Row.

    Dim SHEET As Worksheet
    
    Call Sheet_Arg(Sheet_Spec, SHEET, Sheet_Name)
    
    If Row_End = -1 Then
        Row_End = Last__Row(SHEET)
    End If
    
    If Row_End >= Row_Start Then
        Call Go_To(Row_Start, 1)
        rng = Row_Start & ":" &  Row_End
        SHEET.Rows(rng).Delete
        TS = Last__Row(SHEET)
        If TS <> Row_Start Then Stop ' <-- Error stop here
    End If    
                          
End Sub ' Delete_Data_Rows()
"Last__Row" tells me the same thing as "Ctrl_End" or TS.

What am I missing?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,249
Office Version
365
Platform
Windows
Deleting rows can be achieved like this

VBA Code:
Sub DeleteAFewRows(First As Long, Optional Last As Long)
    Set SHEET = Sheets(1)
    If Last = 0 Then Last = First
    SHEET.Rows(First & ":" & Last).EntireRow.Delete
End Sub
And called like this
VBA Code:
Sub TestMacro()
   Call DeleteAFewRows(33, 39)
End Sub
 

Mackeral

Board Regular
Joined
Mar 7, 2015
Messages
70
Yongle, Thank you so much for your reply.

If there is a better way to send thanks, let me know please.
I am never sure the appropriate way on the different Forums.
 

Mackeral

Board Regular
Joined
Mar 7, 2015
Messages
70
yongle,

I put in your code, but when I do "Ctrl_End" it still shows the last cell as "40T" and the delete range was "2:40".
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,249
Office Version
365
Platform
Windows
works for me - always deletes the rows specified
so, I have no idea why it does not work for you
Perhaps someone else can help
I am withdrawing from this thread
 

Mackeral

Board Regular
Joined
Mar 7, 2015
Messages
70
Went on MrExcel and pulled stuff together and got a Last Row function that really works.
VBA Code:
Function Last__Row(Optional Sheet_Spec = "", _
                    Optional Start_Col As Integer = 1, _
                    Optional ByVal End_Col As Integer = -1)
    ' Returns the last rowl used of the Worksheet by column if desired.

    Dim SHEET As Worksheet
    
    Prog = "Last__Row"
    
    Call Sheet_Arg(Sheet_Spec, SHEET, Sheet_Name)
    
    If Start_Row = 1 And End_Col = -1 Then
        Last_Row = Cells(Rows.Count, 1).End(xlUp)
    
    Else
        If End_Col = -1 Then
            End_Col = SHEET.Range("A1").CurrentRegion.Columns.Count
        End If
    End If
    
    
    For Col = Start_Col To End_Col
        Col_Ptr = Col__Ptr(Col)
        Temp = Cells.Find(What:="*", _
                    After:=Range(Col_Ptr & "1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlValues, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
        Last__Row = Maximum(Last__Row, Temp)
    Next Col
                
End Function ' Last__Row
 

Forum statistics

Threads
1,089,218
Messages
5,406,920
Members
403,111
Latest member
Donbozone

This Week's Hot Topics

Top