Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Losing last row of data when no summary included 1

Status
Not open for further replies.

alwayslrN

IS-IT--Management
Jun 20, 2006
159
US
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?

Code:
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
 
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
Code:
Range(Range("a" & LastRow), Cells.SpecialCells(xlLastCell).End(xlDown)).ClearContents
should make it work and eliminate the .select's
 
our Sub just finds last row of data and deletes it.
You need to detect if the last row is the summary row:
Code:
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
 
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:
Code:
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
 
Thanks Fr and Andy - what happens if there is not a summary, but a formula that acts like a sum?
 
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.
 
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:
Code:
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.
 
Actually, FR's suggestion does the trick. I am no longer losing my data. Thank you all.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top