Reduce the file size via MACRO

This would be a very simple VBA project. Do you want to upload a sample of the data so we can knock one out for you.

Something like

With ActiveSheet
    Range(.Cells(.Rows.Count, 4).End(xlUp).Offset(1,0), .Rows(.Rows.Count)).Delete
End With

This will automatically find the last line and remove lines below it:

Sub AutoRemoveExcessLines()
    Dim i As Long
    Dim FirstColNum As Long
    Dim LastColNum As Long
    Dim LastRow As Long
        
    FirstColNum = 1 '<-- Change this to the first column number of the range of data (A=1, B=2...)
    LastColNum = 10 '<-- Change this to the last column number of the range of data (A=1, B=2...)
    
    'Find the last row ourselves instead of relying on UsedRange
    'Starts at the bottom of the spreadsheet and works up for each column
    'This ensures that excel bugs with UsedRange etc don't affect the result
    LastRow = 0
    With ActiveSheet
        For i = FirstColNum To LastColNum
            If .Cells(.Rows.Count, i).End(xlUp).Row > LastRow Then LastRow = .Cells(.Rows.Count, i).End(xlUp).Row
        Next i
    
        'add an error catcher
        '(only really useful if you have lines at the top of
        'data area that will never be removed, like headers)
        If LastRow > 7 Then ' <-- Change this to whatever the last row of header data is
        
            'we want to delete rows starting after the last row and to the bottom of the worksheet
            .Rows(LastRow + 1 & ":" & .Rows.Count).Delete
        
        End If
    End With
End Sub

And this will remove lines below the current activecell, useful if you want the user to manually select where to delete from. Obvs, this method could be used to delete data if the user isn't careful where they click first.

Sub UserRemoveExcessLines()
    Dim RemoveRow As Long
    
    'get the user selected RemoveRow with EITHER NOT BOTH of these two lines
    RemoveRow = ActiveCell.Row + 1 '<-- The user will have to have a cell selected actually on the last row of data
    RemoveRow = ActiveCell.Row     '<-- The user will have to have a cell selected on the row AFTER the last row of data
    
    With ActiveSheet
        'and we want to delete all rows to the bottom of the worksheet
        .Rows(RemoveRow & ":" & .Rows.Count).Delete
    End With
End Sub

Then just get a button to launch the macro and put it somewhere handy for users to use.

Originally Posted by rz6657

I used the 1st option (automatic vs. user) and it gets stuck at: If .Cells(.Rows.Count, i).End(xlUp).Row > LastRow Then

The tab is not protected and I did updated the ColNum and LastRow

Any ideas why?

Apologies, only seeing this today. When you say "gets stuck at", what's the error?