Delete everything to the right and below my data set

[dougebowl]

I am looking for a Macro that will delete all empty columns to the right of my data set and then delete all rows below my data set.

Row 1 will always contain the column headings to be used to determine the last column (example: Row 1 contains headings in columns A-D, I need all columns E - the last column in spreadsheet deleted)
Column A will always contain a value to be used to determine the last row (example: Column A contains data in Rows 1-4, I need all rows 5 - last row in spreadsheet deleted)

I have found posts that help determine the last column/row, but, I am failing at getting the Macro to select everything to the right and below and delete. I need this to happen as someone could have inadvertently added some formatting to a cell and I need to insure that the only data left in the spreadsheet is the data table.

All advanced help is greatly appreciated.

 

[dougebowl]

For this sample dataset to test the code, I keyed the values in. I have not formulas returning blanks and have not formatted any unused ranges. Below are the results of the msgbox:

lc = 16384
lr = 1048576
ec = 1
er = 1

It appears it is seeing the entire worksheet and not limiting to just my dataset.

 

[Joe4]

It appears it is seeing the entire worksheet and not limiting to just my dataset.

That makes no sense.
And I don't see how ec and er would only be 1.

You are simply using Copy/Paste to copy my code into your workbook, right?
You aren't trying to manually type it all in, right?

A few other questions:
1. How many sheets are in your workbook?
2. Are you on the sheet you want to apply it to when you run the code?
3. What is the name of the module you have placed this VBA code in?
4. Have you pre-formatted the entire sheet?

What might be simplest is if you could upload a copy of the file to a file sharing site (like DropBox) and provide a link to it, so we can see this sample sheet for ourselves to see if we can see what is going on.

 

[dougebowl]

Thanks for all the back and forth. I am doing copy/paste to save the code. I was saving it to my personal.xlsb file as I want to be able to apply this macro to different workbooks.

I saved the code in the current workbook and it worked perfectly. So, I guess now the question is can this be copied to my personal.xlsb file and a button placed on my quick access toolbar (as I have done with other Macros)?

1. There could be multiple worksheets in the workbook. I would like for the code to only work on the active sheet.
2. Yes, I was on the sheet I want the code applied to
3. In the workbook, I placed it in the module ThisWorkbook. In the Personal.xlsb workbook, I placed it in the Sheet 1 module
4. I had not pre-formatted the entire sheet.

Thanks again for all the help with this. It is greatly appreciated!

 

[Joe4]

3. In the workbook, I placed it in the module ThisWorkbook. In the Personal.xlsb workbook, I placed it in the Sheet 1 module

I think this is your problem here. You do NOT want to place it in the "ThisWorkbook" module (then it will only apply to the Personal Macro workbook) or any of the "Sheet" modules (then it only applies to that particular sheet of the Personal Macro workbook). The "ThisWorkbook" and "Sheet" modules are the places to place Event Procedure code, which is VBA code that is automatically triggered upon some event happening (like the opening of a workbook, the update of a cell, the saving of a file, etc).

If you want to use this code in any open workbook, you will want to save it in a general module. From the Project Explorer in VBA, right-click on the "Personal.xlsb" workbook, and select "Insert -> Module". This should create a general module called "Module1". Place your code here, and then you will be able to use it on the active sheet of any open workbook.

 

[dougebowl]

THANK YOU for all the support. I created the module in my personal workbook and copied the code there. It works in any workbook I open. I appreciate you sticking in there to get this to work for me. This will end up saving me quite a bit of time as I perform this function many times a day. All timing savings will add up over time.

 

[Joe4]

You are welcome.
Glad you got it working the way you want!