Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
Hello all- I have a weekly spreadsheet that has 20
columns in it. I only need 3 of the columns, and only specific information from 2 of them. What I want to do is create a macro that will delete the columns I do not need, and filter out the information that is needed. Here is what I currently do to get the information I need: Delete all the columns but EXT PRC, POTYPE, & RS. Then I do autofilter and delete all but LP, PU in POTYPE column. Then I autofilter and delete all but SP in the RS column. Then I turn off autofilter and add the EXT PRC column. There has to be an easier way to do this. I can create formula's for each scenario (except the deletion), but that is still a lot of manual work. Please help ease my suffering? Thanks in advance! Tami |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
have you tried recording a macro while doing all your steps?
Paul D "Tami" wrote in message ... Hello all- I have a weekly spreadsheet that has 20 columns in it. I only need 3 of the columns, and only specific information from 2 of them. What I want to do is create a macro that will delete the columns I do not need, and filter out the information that is needed. Here is what I currently do to get the information I need: Delete all the columns but EXT PRC, POTYPE, & RS. Then I do autofilter and delete all but LP, PU in POTYPE column. Then I autofilter and delete all but SP in the RS column. Then I turn off autofilter and add the EXT PRC column. There has to be an easier way to do this. I can create formula's for each scenario (except the deletion), but that is still a lot of manual work. Please help ease my suffering? Thanks in advance! Tami |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
Yes and the problem I'm having with that is that say my
first spreadsheet has 150 line items and the next time I have to perform it, the file may have 300 line items. I don't know how to "tell" the macro to filter the entire column as opposed to the 150 line items I recorded previously. How do I tell the macro to do a range in a column or row? -----Original Message----- have you tried recording a macro while doing all your steps? Paul D "Tami" wrote in message ... Hello all- I have a weekly spreadsheet that has 20 columns in it. I only need 3 of the columns, and only specific information from 2 of them. What I want to do is create a macro that will delete the columns I do not need, and filter out the information that is needed. Here is what I currently do to get the information I need: Delete all the columns but EXT PRC, POTYPE, & RS. Then I do autofilter and delete all but LP, PU in POTYPE column. Then I autofilter and delete all but SP in the RS column. Then I turn off autofilter and add the EXT PRC column. There has to be an easier way to do this. I can create formula's for each scenario (except the deletion), but that is still a lot of manual work. Please help ease my suffering? Thanks in advance! Tami . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro
Not sure I completely follow you but have you tried:
ActiveSheet.UsedRange or ActiveCell.CurrentRegion Tom Ogilvy did a nice job of explaining the diffence in an older post... "CurrentRegion expands the selection until it reaches a blank row and/or blank column moving in all four directions (like water reaching the edge of an aquarium). UsedRange is the rectangle formed by what Excel considers to be used cells for farthest right and down and earliest left and up. It doesn not necessarily start in cell A1." Choose whichever works for your situation Paul D "Tami" wrote in message ... Yes and the problem I'm having with that is that say my first spreadsheet has 150 line items and the next time I have to perform it, the file may have 300 line items. I don't know how to "tell" the macro to filter the entire column as opposed to the 150 line items I recorded previously. How do I tell the macro to do a range in a column or row? -----Original Message----- have you tried recording a macro while doing all your steps? Paul D "Tami" wrote in message ... Hello all- I have a weekly spreadsheet that has 20 columns in it. I only need 3 of the columns, and only specific information from 2 of them. What I want to do is create a macro that will delete the columns I do not need, and filter out the information that is needed. Here is what I currently do to get the information I need: Delete all the columns but EXT PRC, POTYPE, & RS. Then I do autofilter and delete all but LP, PU in POTYPE column. Then I autofilter and delete all but SP in the RS column. Then I turn off autofilter and add the EXT PRC column. There has to be an easier way to do this. I can create formula's for each scenario (except the deletion), but that is still a lot of manual work. Please help ease my suffering? Thanks in advance! Tami . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with creating a Macro | Excel Discussion (Misc queries) | |||
Creating a New Macro | Excel Discussion (Misc queries) | |||
Creating Macro | Excel Discussion (Misc queries) | |||
Need Help Creating A Macro | Excel Worksheet Functions | |||
Creating a macro | Excel Worksheet Functions |