Selection.ClearContents clearing needed row

[alwayslrN]

I two spreadsheets in which I use the same macro, but for one of the spreadsheets I lose a row of data that is needed. Here is a simple example:

Spreadsheet 1

A B C
12345 500 200
12453 500 300
145263 600 700
1200

Spreadsheet 2

A B C
12745 200 100
12453 550 350
14533 600 600

Here is my 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

For the first spreadsheet this works fine. I removes the data in the last row, but for the second spreadsheet, since there is not data in the last row, the program selects the selected row and the row above then deletes the data. In the example above it would delete the last line containing
14533 600 600. I need that row. Should I change the above or should I do a check for data in the last row cell C and then have the code do something based on that?

Thanks

 

[PHV]

Replace "A" with "C"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alwayslrN]

Thanks PHV, but I will still have the same problem if I do that. In the simple example above, for spreassheet 2, the 600 would be cleared.

The issues is being caused by

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

It looks for the last cell with a value. Since the same macro is used for the two spreadsheets, and spreadsheet 2 does not have a subtotal it moves up to the last row with a value which a row that I need and clears it. I need to use something other than

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

or use an If-else statement.

 

[PHV]

Have you tried to play with the UsedRange property ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[macropod]

Hi alwayslrN,

Try something like:[/code]
Sub ClrSubTtl()
Dim LastRow As Long
With ActiveSheet
LastRow = .UsedRange.Rows.Count
If .Range("A" & LastRow).Value = "" Then .Rows(LastRow).EntireRow.Clear
End With
End Sub[/code]
Cheers

[MS MVP - Word]