+ Reply to Thread
Results 1 to 16 of 16

Complex macro? or Multiple macros? Manual? CHALLENGE!!

  1. #1
    Registered User
    Join Date
    10-05-2012
    Location
    MN
    MS-Off Ver
    Excel 2011
    Posts
    6

    Complex macro? or Multiple macros? Manual? CHALLENGE!!

    I have a zip code spreadsheet with columns A-CH, and 80,100 rows and I need to manipulate it.

    I have very limited macro/vb experience and have spent a good 6 hours trying to accomplish the following:

    1. Delete ROWS where the contents of COLUMN BN = 'Y'

    2. Delete ROWS where the contents of COLUMN W = 'AA' & 'AE' & 'AP'

    3. Delete COLUMNS titled 'MSA, PMSA, CSA, CBSA, CBSA_Div, CBSA_Type, CBSA_Name, MSA_Name, PMSA_Name, Region, Division, MailingName, BusinessFirstQuarterPayroll, BusinessAnnualPayroll, BusinessEmploymentFlag, GrowthRank, GrowthHousingUnits2009, GrowthHousingUnits2010, GrowthIncreaseNumber, GrowthIncreasePercentage, CBSAPop2003, CBSADivPop2003, CongressionalDistrict, CongressionalLandArea, DeliveryResidential, DeliveryBusiness, DeliveryTotal, CSAName, CBSA_Div_Name, PopulationEstimate, LandArea, WaterArea, BoxCount, SFDU, MFDU, Population, AliasIntroDate, ZIPIntroDate, HouseholdsPerZipCode, WhitePopulation, BlackPopulation, HispanicPopulation, AsianPopulation, HawaiianPopulation, IndianPopulation, OtherPopulation, MalePopulation, FemalePopulation, PersonsPerHousehold, AverageHouseValue, IncomePerHousehold, MedianAge, MedianAgeMale, MedianAgeFemale, StateFullName'

    PLEASE HELP me before I pull all my hair out. Im even willing to send you my spreadsheet as there is no private data in it ... Yes, I could do this all manually, but I really want to see this work with a macro that can be used each month.

    THANKS!

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,966

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    Try this

    Please Login or Register  to view this content.
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,244

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    You could send me your spreadsheet and I could send you my Paypal details ...

    You're not asking for help creating a formula or fixing a coding error. This is a mini-project. Not difficult in concept but a few hours work to code and test.

    You could, of course, record a macro while you carry out the task(s) manually. Then come to the forum for help tidying and tweaking the code.

    If I were doing it, I'd probably set up an array of columns to be deleted and loop through that. The array could be hard coded or defined on a "control" sheet.

    Deleting rows could be done using Autofilter to select the rows and deleting them.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    10-05-2012
    Location
    MN
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    THANK YOU MRICE!

    So when I run this, I get "For control variable already in use" at:

    For N = Cells(Columns.Count, 1).End(xlToLeft).Column To 1 Step -1

    Any ideas?
    Last edited by kamgrn; 10-05-2012 at 05:41 PM. Reason: added quotes to error

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    Hello

    It appears MRice left out a "Next N" statement. I have added it in the code below.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  6. #6
    Registered User
    Join Date
    10-05-2012
    Location
    MN
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    MRice & Lieth Ross - Thank you so much for your help!

    After applying the update from Lieth, the Macro ran without error. After reviewing my spreadsheet for the changes, I found that it successfully removed the Y ROWS from column BN, and it also removed the AA, AE, & AP ROWS from column W.

    But it did not remove the COLUMNS from the 3rd statement.

    Any further advice is GREATLY appreciated!!

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    Hello kamgrn,

    Do the statements appear individually in the cell or in combination with other words?

  8. #8
    Registered User
    Join Date
    10-05-2012
    Location
    MN
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    I realized that there was a space before the column titles in the statement above. So I removed those spaces.. and it still did not remove the columns.

    Would it be easier if I provided the actual columns vs the column titles:
    “AI”, “AJ”, “AK”, “AL”, “AM”, “AN”, “AO”, “AP”, “AQ”, “AR”, “AS”, “AT”, “AW”, “AX”, “AY”, “AZ”, “BA”, “BB”, “BC”, “BD”, “BE”, “BF”, “BG”, “BH”, “BI”, “BJ”, “BK”, “BO”, “BP”, “BR”, “BS”, “BT”, “BX”, “BY”, “BZ”, “C”, “CD”, “CC”, “D”, “E”, “F”, “G”, “H”, “I”, “J”, “K”, “L”, “M”, “N”, “O”, “P”, “Q”, “R”, “S”, “X”

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    Hello kamgrn,

    Are these labels in row 1 of the columns?
    Are there any merged columns or rows?
    Can you post a copy of your workbook?

    To Attach a File:
    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.

  10. #10
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    So maybe:

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  11. #11
    Registered User
    Join Date
    10-05-2012
    Location
    MN
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    No columns or rows are merged. The first couple rows of the worksheet are attached (i deleted about 50,000 rows before upload to reduce file size)
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    10-05-2012
    Location
    MN
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    Hold the press!! I removed the 3rd statement and replaced it with JBEAUCAIRES Range statement .. AND IT WORKED!!! YOU ALL HAVE MADE MY DAY!!!!!! Thanks so much for your help and participation!! Truely appreciated! I will be able to sleep this weekend!

  13. #13
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,124

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    I know this thread has been closed, but I am keen to know and understand why Mrice’s has not worked.
    I think the reason could be this line of code:
    Please Login or Register  to view this content.
    If step over the code using F8, assuming n as long shows one column only, despite there are appear to be over 30 columns. The code looks at cells(1,1) and ends as it could not find any word which matches with the select case. To test it, I have added "ZipCode" in to the case statement and run the code. It deleted column “A” as the code find "ZipCode" in cells(1,1).
    I have also run some msg box
    Please Login or Register  to view this content.
    N returns 8
    So, the question is why the code returns a single column? Is it because the columns are merged in to one column, i.e. Column “A”?
    I also run unmerge code and there is none.
    May be as a newbie to VBA, I still do not understand how the last columns work.
    I had expected
    Please Login or Register  to view this content.
    Row should show the last row and
    Please Login or Register  to view this content.
    should also shows the last column.
    Back to the drawing board then!
    Last edited by AB33; 10-06-2012 at 12:33 AM.

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,244

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    Please Login or Register  to view this content.
    The format for Cells is Cells(Row, Column) so that statement is starting in column A (1), row whatever. The Case statement also has a leading space for all the headers except the first. I suspect that's not really the case.

  15. #15
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,124

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    TMShucks!
    You have an eye for details. I written half a page on a single line as I was unable to spot a single error. I thought I was gone made. I am not sure if Jerry and Leith had spotted this error.
    It should have been like this:

    Please Login or Register  to view this content.
    Now, I can see more than 30 columns, ie 87 columns. The code works too.
    I do not think you need more than 5* on your name as you have already got them, but will double them if you wish.
    Thanks all
    Last edited by AB33; 10-06-2012 at 09:18 AM.

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,244

    Re: Complex macro? or Multiple macros? Manual? CHALLENGE!!

    @AB33: Thanks for the rep, and the kind words.

    I said, in my initial response, that you can spend a few hours developing *and testing* something like this.

    The OP said there were/are 80,100 rows of data. The code in post two loops through the rows twice, once on column BN and once on column W. That could take a while ... I think that would be better done with an Advanced Filter (that I'd probably create on a hidden sheet) and delete the filtered rows.

    Then use JB's line of code to delete the columns, assuming they are fixed. if they're not, I'd list the ranges in a (hidden) worksheet and build a range variable and delete that.

    With only half a dozen rows of test data, it's not really feasible to determine how quick or slow the code will be in real life.

    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1