Cannot remove extra lines with VBA or manually!!??

[logandiana]

I've got code that takes a sheet copies it.
Then uses

With billPO
LR1 = billPO.Cells(Rows.Count, 3).End(xlUp).Row
.Range(.Rows(LR1 + 1), .Rows(LR1 + 1).End(xlDown)).Delete
End With

To delete everything under the last row (usually around 700 rows).
Then the workbook is saved.

When I go check the saved workbook after the procedure. I've got my data but after the 700 or so rows of data, I've got blank rows all the way down to the bottom (1048576)
Not sure why this is happening this time in particular as this method has always worked before.
BUT here's the kicker.
When I manually open the workbook. Go to the 1st blank row after the last row of data.
I select the row, then do a ctrl shift down, right click and Delete. All the empty rows appear to delete.
I save and close. I have done this method without problem before as well.

But I go back into the workbook and the blank rows are still there.

Not sure what is happening.

 

[jasonb75]

I've got blank rows all the way down to the bottom (1048576)

How far down does the sheet go if you drag the vertical scrollbar? (Drag it, don't use the scroll arrows, mouse wheel, or any other alternative).
If it only goes to the end of your data then those rows are empty, not blank (there is a difference).

1048576 is the number of rows in a sheet, there is nothing you can do to change that, they will always be there. If you go back to using excel 2003 or older then you can reduce it to 65536 rows but that is all.

 

[Marc L]

Check the value of LR1 first as the End property used could not work as you expect for …​

​

Only if LR1 is correct then try​

With billPO
    .Rows(.Cells(.Rows.Count, 3).End(xlUp).Row + 1 & ":" & .Rows.Count).Delete
End With

 

[logandiana]

How far down does the sheet go if you drag the vertical scrollbar? (Drag it, don't use the scroll arrows, mouse wheel, or any other alternative).

It goes to 1048576

Check the value of LR1 first as the End property used could not work as you expect for

Stepping through, LR1 is 701

With billPO
    .Rows(.Cells(.Rows.Count, 3).End(xlUp).Row + 1 & ":" & .Rows.Count).Delete
End With

Nope, this does the same thing.


Once I open the workbook, if I do a Ctrl END it takes me to M1048576
But the only data is always in A1:M701

 

[cmowla]

What you want to do is what's called "resetting the used range".
From Excel resetting "UsedRange" :

Sub DeleteUnused()
Dim myLastRow As Long
Dim myLastCol As Long
Dim dummyRng As Range
Dim AnyMerged As Variant
'http://www.contextures.on.ca/xlfaqApp.html#Unused
'Helps to reset the usedrange by deleting rows and columns AFTER your true used range

    'Check for merged cells
    AnyMerged = ActiveSheet.UsedRange.MergeCells
    If AnyMerged = True Or IsNull(AnyMerged) Then
        MsgBox "There are merged cells on this sheet." & vbCrLf & _
               "The macro will not work with merged cells.", vbOKOnly + vbCritical, "Macro will be Stopped"
        Exit Sub
    End If

    With ActiveSheet
        myLastRow = 0
        myLastCol = 0
        Set dummyRng = .UsedRange
        On Error Resume Next
        myLastRow = _
        .Cells.Find("*", after:=.Cells(1), _
                    LookIn:=xlFormulas, lookat:=xlWhole, _
                    searchdirection:=xlPrevious, _
                    searchorder:=xlByRows).Row
        myLastCol = _
        .Cells.Find("*", after:=.Cells(1), _
                    LookIn:=xlFormulas, lookat:=xlWhole, _
                    searchdirection:=xlPrevious, _
                    searchorder:=xlByColumns).Column
        On Error GoTo 0

        If myLastRow * myLastCol = 0 Then
            .Columns.Delete
        Else
            .Range(.Cells(myLastRow + 1, 1), _
                   .Cells(.Rows.Count, 1)).EntireRow.Delete
            .Range(.Cells(1, myLastCol + 1), _
                   .Cells(1, .Columns.Count)).EntireColumn.Delete
        End If
    End With

End Sub

 

[logandiana]

What you want to do is what's called "resetting the used range".
From Excel resetting "UsedRange" :
VBA Code:
Sub DeleteUnused()
Dim myLastRow As Long
Dim myLastCol As Long
Dim dummyRng As Range
Dim AnyMerged As Variant
'http://www.contextures.on.ca/xlfaqApp.html#Unused
'Helps to reset the usedrange by deleting rows and columns AFTER your true used range

'Check for merged cells
AnyMerged = ActiveSheet.UsedRange.MergeCells
If AnyMerged = True Or IsNull(AnyMerged) Then
MsgBox "There are merged cells on this sheet." & vbCrLf & _
"The macro will not work with merged cells.", vbOKOnly + vbCritical, "Macro will be Stopped"
Exit Sub
End If

With ActiveSheet
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0

If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With

End Sub

Nope, same result

 

[jasonb75]

I remember the same thing in an old thread which was caused by something like the entire column, all but 1 row having a custom row height or something equally silly sounding. I'll have a look for the thread and post a link to it, might take me a while to find it though.

 

[cmowla]

Nope, same result

Here's another algorithm that will definitely work: (From Excel VBA usedRange Property and reset usedRange .)

Sub Test__ResetUsedCellRange()
Call ResetUsedCellRange
End Sub
Sub ResetUsedCellRange(Optional ByVal lastRowNumber As Long, Optional ByVal lastColumnNumber As Integer)

'but the following code was from https://access-excel.tips/excel-vba-usedrange-property/.

Application.EnableEvents = False
Dim rng As Range
For Each rng In ActiveSheet.usedRange
    If rng.MergeCells = True Then
        If rng.Value = "" Then rng.Value = ""
    Else
        If rng.Value = "" Then rng.ClearContents
    End If
Next
Application.EnableEvents = True

'If the user didn't specify a last used row and/or column number.
If lastColumnNumber = 0 Then lastColumnNumber = LastUsedColumnNumberAccordingToExcel(ThisWorkbook, ActiveSheet.name)
If lastRowNumber = 0 Then lastRowNumber = LastUsedRowNumberAccordingToExcel(ThisWorkbook, ActiveSheet.name)

Dim r As Long
r = 1
Do While r <= lastRowNumber
    If Application.WorksheetFunction.CountA(Cells(r, lastColumnNumber).EntireRow) <> 0 Then
        GoTo Line1
    Else
        Cells(r, lastColumnNumber).EntireRow.Delete
    End If
    r = r + 1
Loop

Line1:
Dim C As Integer
C = 1
Do While C <= lastColumnNumber
    If Application.WorksheetFunction.CountA(Cells(1, C).EntireColumn) <> 0 Then
        Exit Sub
    Else
        Cells(1, C).EntireColumn.Delete
    End If
    C = C + 1
Loop

End Sub

 

[jasonb75]

Here's another algorithm that will definitely work

A very bold statement, hopefully a true one as well.

 

[Fluff]

Try selecting all cells & change the row height slightly, then save the workbook & see if that helps.