+ Reply to Thread
Results 1 to 8 of 8

Reducing table size in a macro

  1. #1
    Registered User
    Join Date
    06-04-2014
    Location
    Newcastle, Australia
    MS-Off Ver
    2010
    Posts
    63

    Reducing table size in a macro

    Hi, I have searched and cannot find an answer. I am also trying to teach myself VBA so even something as simple as what I am trying to do seems hard to me (at the moment).

    I have two columns of data, column A and column B that are copied from another workbook.

    The columns generally be anywhere from 5 to 800 rows that has data in them. Some of these values are a result of a formula, so they may have "" as a value. Any cells in column A that has a "" result in it, then column B will also be "".

    I have the code below that will delete the "" from columns A and B but will also attempt to do the same to column C which performs funtions based in Columns A and B.

    So, after all that, how would I get this code to only delete "" from columns A and B, not Column C as well.

    Please Login or Register  to view this content.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Reducing table size in a macro

    Hi, good of you to take up VBA, it's a challenge and it's challenging, I can appreciate that.
    If I understand you correctly you don't want to delete the columns, you just van to clear then, except for Column C because it contains a formula.
    When checking cell contents you can use the choice .HasFormula, this returns true so if you do a check on that you can skip cells that have formulas
    You can also use SpecialCells() , google for this en you get a list of options to select only those that have formulas or just not have fomulas.
    Another thins is working with tables like you're doing.
    This is a link I found long time ago and have kept it in my bookmarks because it sheds light on what you can do with tables and manipulating the data in them
    https://www.thespreadsheetguru.com/b...t-excel-tables

    I'll check your file and see what I can do to help you but in the meanwhile try the suggestions and see where they get you.
    VBA requires two basic important 'ingredients' TIME and PATIENCE and besides that just try things out, it's fun
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Reducing table size in a macro

    My mistake, seeing the fact that you used tbl as a variable i made the incorrect assumption that you use ListObjects.
    My bad.
    I checked the 'table' in DI, What is it you want to achieve?
    Do you want to delete the empty rows?

  4. #4
    Registered User
    Join Date
    06-04-2014
    Location
    Newcastle, Australia
    MS-Off Ver
    2010
    Posts
    63
    Thanks for taking the time to look at this for me. I’m slowly getting the hang of things.

    Basically any cells in columns A and B that are empty I want to delete and move the cells up.

    Hopefully this clarifies things.

    Thanks.

  5. #5
    Registered User
    Join Date
    06-04-2014
    Location
    Newcastle, Australia
    MS-Off Ver
    2010
    Posts
    63

    Re: Reducing table size in a macro

    I have found an alternative that works for me. I appreciate your time on this.

    This is the code I ended up using
    Please Login or Register  to view this content.

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Reducing table size in a macro

    What are the cells on the right for with column letter and row?
    TIP : When you want to use vba to delete rows I always do it bottom to top so you can use the last reference as starting point, you do a for x is latrow to row 2 or where you want to step with s step -1

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Reducing table size in a macro

    This will do it
    Please Login or Register  to view this content.

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Reducing table size in a macro

    Throw them in an array instead of copying back and forth.
    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Reducing File Size
    By zmster2033 in forum Excel General
    Replies: 7
    Last Post: 01-06-2017, 01:30 AM
  2. Excel 2007 : Reducing size of a table
    By KillieChris in forum Excel General
    Replies: 0
    Last Post: 07-23-2011, 04:32 PM
  3. Reducing size of worksheet after using macro
    By Nicnic in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-21-2008, 06:43 AM
  4. reducing formula size
    By mccardled in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-22-2007, 02:47 AM
  5. Can I reducing tab size...
    By Centrum in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-18-2007, 07:26 AM
  6. [SOLVED] Reducing File Size
    By Jay in forum Excel General
    Replies: 8
    Last Post: 07-23-2006, 06:25 AM
  7. [SOLVED] reducing file size
    By FBr in forum Excel General
    Replies: 0
    Last Post: 03-08-2006, 09:35 PM

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