Find and select last plus one row in a worksheet?

[davidwh000]

I would like to get the code for a macro that will allow me to automatically find and select the last plus one row in a worksheet and then use that to select to the end of the worksheet and clear all from that point forward please. I would also like to do the same for columns.
The report that is being produced has variable row count and has formatting in the blank cells which I would like to delete before using the report.
David

 

[davidwh000]

Weaver

I have had a little play with your original suggestion and by changing delete to clear it works clearing all from the rows beyond the end of my spreadsheet but only clears one column, the column to the right of the last populated cell. How do I clear the rest of the columns to the end of the sheet?

David

 

[Weaver]

What did pbolton's code do? I always figure, if it works, don't knock it. But in answer to your question,

Sub clearFormatsFromBlankCells()
    Application.ScreenUpdating = False
    lastcol = Cells.Find(what:="*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
    lastrow = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    Range(Cells(lastrow + 1, 1), Cells(Rows.Count, 1)).EntireRow.Delete
    Range(Cells(1, lastcol + 1), Cells(1, [U][I][B]lastcol + 1[/B][/I][/U])).EntireColumn.Delete
End Sub

the underlined bit should read columns.count.
I didn't bother trying to fix it since you were getting that 'function call' error and I only just this minute noticed there was an issue (bad testing setup - I had a grid of formatted cells, only some of which had data in and I'm guessing the last but one column did, so when the column to the right was deleted, there was no obvious way of knowing that was the only column deleted!).
Thanks for taking an interest.
DW

 

[davidwh000]

Thanks Mate
Much appreciated, saved me a lot of time sorting out a pretty mundane manual task. These forums do work especially with members like yourself and pbolton
David