Macro to Delete Columns

[Kim B]

Hi All. I am looking for a simple macro auto fit columns and delete columns N,P,Q,R,T so leaving O,Q,S,U

It will always be those columns to delete but the trick is the data is pulled down daily so if it possible to have the macro run on each new workbook as it is pulled down

I have tried using record macro but there seems to be no option in store macro in drop down that will work with a new workbook. if I select new workbook, it's not there unless I am doing something wrong when setting up the record macro.. I was usin shortcut key r

As always thank you in advance

 

[Burrgogi]

Hi All. I am looking for a simple macro auto fit columns and delete columns N,P,Q,R,T so leaving O,Q,S,U

The following is a macro that will delete those macros. You'll need to store it in the Personal Macro Workbook; this will make it available so that you can run it on any existing or new workbook.

Sub Delete_columns()
Dim rng As Range
With ActiveSheet
    Set rng = Union(.Range("N:N"), .Range("P:P"), .Range("Q:Q"), .Range("R:R"), .Range("T:T"))
End With
rng.Delete
End Sub

If you're not familiar with using the Personal Macro Workbook, follow this guide here:

How to create, view, and edit macros in Personal Macro Workbook

 

[Burrgogi]

Just one more thing to add. It sounds like you do this quite often. I would advise saving my macro in the aforementioned workbook, then assign a button for it on your QAT (quick access toolbar). That way you can just click on 1 button to run it instead of having to go through the macro run menu. It'll save you a little bit of time. The following screenshot is an example of 2 macros that are on my QAT that I use quite frequently. If you've never done this and need instructions, follow this guide.

How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar - Excel Campus

You can quickly and easily add buttons to the Excel ribbon or Quick Access tolbar to run customized macros and make your job simpler.

www.excelcampus.com

 

[Kim B]

Can we add autofit to it?

Thank you

 

[Burrgogi]

Can we add autofit to it?

Thank you

All of them or just specific ones?

 

[Burrgogi]

Can we add autofit to it?

Thank you

This will autofit all the used columns in the worksheet.

Sub Delete_columns()
Dim rng As Range
Sheets(1).UsedRange.Columns.AutoFit     '<--------  adjust the number in the parenthesis as needed
With ActiveSheet
    Set rng = Union(.Range("N:N"), .Range("P:R"), .Range("T:T"))
End With
rng.Delete
End Sub

The current code runs on the 1st workshseet within your workbook. If the particular worksheet in question happens to be somewhere else (3rd for example), you'll have to line number 3 to the following:

Sheets(3).UsedRange.Columns.AutoFit

If you'd rather use the name of the worksheet, you can use this:

Sheets("test").UsedRange.Columns.AutoFit

Replace the word test with the actual name of the worksheet in question.

 

[Kim B]

Thank you works perfectly!

Is it possible execute the formatting macro and incorporate extracting data to separate workbooks? Currently this would need to be run multiple times to get each location by itself. Ideally the outcome would be to run a macro that "cuts" and "pastes the data for each criteria to it's own workbook. Please note the grouping on the right. It downloads in this format. If i have to put a separate request, please let me know.

Thank you

 

[Burrgogi]

The following code copies the information to a new workbook as requested.

Sub Delete_columns()
Dim rng As Range
ActiveSheet.Copy
With ActiveSheet
    Set rng = Union(.Range("N:N"), .Range("P:R"), .Range("T:T"))
End With
rng.Delete
Sheets(1).UsedRange.Columns.AutoFit    '<---------- adjust the number in parenthesis as needed
End Sub

Is it possible execute the formatting macro and incorporate extracting data to separate workbooks? Currently this would need to be run multiple times to get each location by itself. Ideally the outcome would be to run a macro that "cuts" and "pastes the data for each criteria to it's own workbook. Please note the grouping on the right. It downloads in this format. If i have to put a separate request, please let me know.

It's unclear what you mean by criteria. What are those criterias? Which columns (or rows) are they displayed in?

Also, it would help if you explained what your ultimate goals are instead of making your request in bits & pieces at a time.

 

[Kim B]

I understand and I apologize. The request came to me in pieces as well. The criteria means the location. IE: ABC XX is a location. ABC XX location 2 is a seperate location. Row 8 93 172 181 251 are all different locations. I am wondering if it is possible to extract all the data under each location to be dumped into it's own workbook.

 

[Burrgogi]

I understand and I apologize. The request came to me in pieces as well. The criteria means the location. IE: ABC XX is a location. ABC XX location 2 is a seperate location. Row 8 93 172 181 251 are all different locations. I am wondering if it is possible to extract all the data under each location to be dumped into it's own workbook.

It looks like those locations are located in column A - correct? How are those row groupings identified & created? From the screenshot, it looks like each location has the word "Group" at the end of the name group. So that's easy to sort out. What's unclear is what items go under each separate group and what rules you are using to group each one.

Your request is getting complex enough that you should really upload a sample of the original workbook. From the screenshot you provided it looks like there are several row groupings. I assume that each group is its own separate location. The answer is yes, each location can be pulled from the original workbook and separated into different workbooks of its own.

Before I go any further, I advise 2 things:

1) Create a separate thread. You started this thread asking for a macro to autofit and delete some columns. What you're asking for now is completely different and a little bit more involved compared to the original request.

2) Go back to the person who is making these requests. Tell him or her to outline ALL the requirements upfront instead of doing it in bits & pieces at a time. It will help me or whoever picks up the new thread to create a more efficient script. I have many things to do this weekend and I don't have time to check back on this thread to see if there are other additional requests.