End (xlDown).offset error

[Guzzlr]

Hello
I have:

Range("A5").End(xlDown).Offset(1).Resize(ActiveSheet.UsedRange.Rows.Count).EntireRow.Delete

I'm getting this error:

What I am wanting, is to use column A, and drop to the bottom and delete all rows up to where the data starts.
I was using A5, because that is where the data header begins. There are some blank cells before A5, so I was not sure if I can simply use Range ("A:A") due to a couple of blank cells in column A above A5.

Where is my mistake that is causing this error?
Thank you for the help

 

[rlv01]

If you attempt to execute that line of code when you have no data after A5 it will select the last row in the worksheet, then try offset one row past that which will produce the error. Think about how you want to deal with the case of no data below row 5. For example;

        If Range("A5").End(xlDown).Address <> "$A$1048576" Then
            Range("A5").End(xlDown).Offset(1).Resize(ActiveSheet.UsedRange.Rows.Count).EntireRow.Delete
        Else
            MsgBox "No rows to delete"
        End If

 

[Joe4]

To make it a little more generic, you could just replace this line:

If Range("A5").End(xlDown).Address <> "$A$1048576" Then

to this:

If Range("A5").End(xlDown).Row < Rows.Count Then

That would then work for any version of Excel (in case you had any users using really old versions).
Or if someday Excel is to increase the row limit again!

 

[Guzzlr]

If you attempt to execute that line of code when you have no data after A5 it will select the last row in the worksheet, then try offset one row past that which will produce the error. Think about how you want to deal with the case of no data below row 5. For example;

        If Range("A5").End(xlDown).Address <> "$A$1048576" Then
            Range("A5").End(xlDown).Offset(1).Resize(ActiveSheet.UsedRange.Rows.Count).EntireRow.Delete
        Else
            MsgBox "No rows to delete"
        End If

I have been messing around with this, and I have code which seems to be working:

With Sheets("Tech Description Report")
        LastRow = .Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row
        .Rows(LastRow + 1 & ":" & Rows.Count).Delete
    End With

What is your opinion of the code I have pieced together, is it a good fit, or will it get me into trouble compared to yours?
Thank you for your time

 

[rlv01]

What is your opinion of the code I have pieced together

Too many unknowns about your data and your requirements for me to comment. For example, previously you indicated that A5 was where the search for the end needed to start because that was your header row, but now with you second method you don't seem to care. Perhaps you don't realize that if col A has less that 5 row of data, you new code will delete your header row. Or perhaps it does not matter any more. Only you can decide whether it meets all your needs. The main thing that will determine whether whatever method you choose is a success is how it handles the 'edge' cases. No data in Column A, for example.