End (xlDown).offset error

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
982
Office Version
  1. 2021
Platform
  1. Windows
Hello
I have:
VBA Code:
Range("A5").End(xlDown).Offset(1).Resize(ActiveSheet.UsedRange.Rows.Count).EntireRow.Delete

I'm getting this error:
1714745806619.png


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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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;
VBA Code:
        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
 
Upvote 0
To make it a little more generic, you could just replace this line:
VBA Code:
If Range("A5").End(xlDown).Address <> "$A$1048576" Then
to this:
VBA Code:
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!
 
Upvote 0
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;
VBA Code:
        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:

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top