Losing last row of data when no summary included 1

[alwayslrN]

I have the following piece of code in an Excel 2002 macro. What it basically does is clear out any data that goes past the last row of records, thus basically clearing out in summary values.

The issue I have is - if a file does not have summary values - it clears out my last of row of data, which is needed. I hope to adjust my current code so I do not lose that last line?

Sub FindLastRow()
    Dim LastRow As Long
    Dim wlast As Integer
    
    LastRow = Cells(Cells.Rows.Count, "a").End(xlUp).Offset(1).Row
    wlast = LastRow
    Range("a" & LastRow).Select

    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.ClearContents
End Sub

 

[Fr33dan]

As a good habit try and stay away from .Select (and .Activate). There's probably a better way but replacing your last 3 lines with

Range(Range("a" & LastRow), Cells.SpecialCells(xlLastCell).End(xlDown)).ClearContents

should make it work and eliminate the .select's

 

[Andrzejek]

our Sub just finds last row of data and deletes it.
You need to detect if the last row is the summary row:

Sub FindLastRow()

Dim LastRow As Long
Dim wlast As Integer
    
LastRow = Cells(Cells.Rows.Count, "a").End(xlUp).Offset(1).Row
wlast = LastRow
Range("a" & LastRow).Select

[blue]If Range("a" & LastRow) is summary row Then[/blue]
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.ClearContents
[blue]End If[/blue]

End Sub

Have fun.

---- Andy

 

[Fr33dan]

That could work but then you would need to have some way of knowing how long your data is(wasn't needed in original)But if that's not a problem you should still eliminate the .select's:

Sub FindLastRow()

Dim LastRow As Long
Dim wlast As Integer
    
LastRow = Cells(Cells.Rows.Count, "a").End(xlUp).Offset(1).Row
wlast = LastRow

If Range("a" & LastRow) is summary row Then
    Range(Range("a" & LastRow), ActiveCell.SpecialCells(xlLastCell)).ClearContents
End If

End Sub

 

[alwayslrN]

Thanks Fr and Andy - what happens if there is not a summary, but a formula that acts like a sum?

 

[alwayslrN]

I should add, for some files that use this procedure, not all of the extra data are summaries. There could also be notes and or other data that is not part of the core tabular set of desired records.

I am afraid of making the "if" statement so specific and then jeopardizing my files that are currently working correctly.

 

[Fr33dan]

If you want to delete all of the data after the final desired data then your best bet it going to be what I posted originally. It will delete everything past the final row that has data in column A:

Sub FindLastRow()

Dim LastRow As Long

LastRow = Cells(Cells.Rows.Count, "a").End(xlUp).Offset(1).Row

Range(Range("a" & LastRow), _
    Cells.SpecialCells(xlLastCell).End(xlDown)).ClearContents

End Sub

Not sure what wlast was for so I got rid of it. I've tested the code and it works for me.

 

[alwayslrN]

Actually, FR's suggestion does the trick. I am no longer losing my data. Thank you all.