How to get rid of "ghost" rows without saving the file?

[Kyrpo]

Hi Excel Experts,

I'm currently trying to create a macro to format some data.

The data we receive are usually quite consistent except for some "ghost" rows at the end of the worksheet.

The only way I found to get rid of those is the follow...

 Dim LR1 As Integer 'last cell used in worksheet
 LR1 = Range("A1").SpecialCells(xlLastCell).Row
 Dim LR2 As Integer 'last cell in region
 LR2 = Range("A1").CurrentRegion.Rows.Count
    
    If LR1 > LR2 Then
    
       Rows(LR1 & ":" & LR2 + 1).delete

Unfortunately... the only way that I've found to make excel receive and accept the input that those "ghost" row have been deleted is to save the file, otherwise even if deleted if I run the same macro again those "ghost rows" will still be there.

Is there any way to make Excel realize that those "ghost" rows have been deleted without saving the file?


Thanks for your support and enjoy your weekend.

 

[NoSparks]

maybe just

Activesheet.Usedrange

will do

 

[Kyrpo]

Works like a charm. Thanks you so much!!

 

[MARK858]

Another option as UsedRange also has a habit of retaining a memory especially when cells were previously formatted (although referring to it seems to work).

Please note the code below is set up to work through all the sheets in the workbook at the moment.

Sub LoseThatWeight()

    Dim x As Long, Lastrow As Long, LastCol As Long

    Application.ScreenUpdating = False

    On Error Resume Next

    For x = 1 To Sheets.Count
        With Sheets(x)
            Lastrow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
            .Range(.Cells(Lastrow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
        End With
    Next x
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

 

[Kyrpo]

Thanks Mark, if I end up having trouble with the UsedRange function I'll switch to the one you proposed. Thanks again.

 

[MARK858]

You're welcome, have a look at the link below where it explains about referencing the UsedRange as I mentioned for resetting the last row (it is actually referencing the Rows.Count property to be exact).
Excel Tips From John Walkenbach: Automatically Resetting The Last Cell