+ Reply to Thread
Results 1 to 9 of 9

Limit entirerow columns with VBA code

  1. #1
    Registered User
    Join Date
    10-20-2014
    Location
    The Netherlands
    MS-Off Ver
    2007
    Posts
    29

    Question Limit entirerow columns with VBA code

    Consider the following:

    Please Login or Register  to view this content.
    It is a known fact that Excel 2007 has up to 16384 rows per sheet and that one does not want to format cells that are completely unused. Is there a way, using VBA code, to limit Excel to only use, say, 20 columns?

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Limit entirerow columns with VBA code

    Maybe:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-20-2014
    Location
    The Netherlands
    MS-Off Ver
    2007
    Posts
    29

    Re: Limit entirerow columns with VBA code

    Thanks for your suggestion, John.

    However, I'm looking for something I don't need to re-write for all the ranges. Furthermore, not all ranges are the same size or start on the same column. Elsewhere in my code I created a function that grabs rowscolumns 3 to 20 or so and then handles them. I cannot use this particular solution to the challenge (problem sounds so negative! :P ) I have now.

    Therefore, I would like to find something that limits the entire sheet to 20 columns, or so.
    Last edited by ChrisPatterson; 06-29-2015 at 02:55 AM.

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Limit entirerow columns with VBA code

    Can you provide a sheet with some samples of your ranges? Are they named ranges?

  5. #5
    Registered User
    Join Date
    10-20-2014
    Location
    The Netherlands
    MS-Off Ver
    2007
    Posts
    29

    Question Re: Limit entirerow columns with VBA code

    Some of the ranges are named, yes. Some however are offsets of ranges, for example:

    Please Login or Register  to view this content.
    When this is the case it's easiest to format the whole line as strikethrough:

    Please Login or Register  to view this content.
    In some instances I use the merged area:

    Please Login or Register  to view this content.
    I know in this example you don't need to add mergearea, because it looks at the range itself already. However, when used in a function with differing input range styles this can be handy.

    I've attached a file to simply display what I mean.
    Limit strikethrough.xlsx

  6. #6
    Registered User
    Join Date
    10-20-2014
    Location
    The Netherlands
    MS-Off Ver
    2007
    Posts
    29

    Question Re: Limit entirerow columns with VBA code

    This is a piece of code where I use the fact that it is a merged area or not in a particular way:

    Please Login or Register  to view this content.

  7. #7
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,370

    Re: Limit entirerow columns with VBA code

    Hi ChrisPatterson,
    maybe you just need to remove the entirerow
    try to replace it
    Please Login or Register  to view this content.
    with this
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    10-20-2014
    Location
    The Netherlands
    MS-Off Ver
    2007
    Posts
    29

    Re: Limit entirerow columns with VBA code

    Thanks for your suggestion, Nilem.

    Unfortunately that also won't work. The reason why I want to do this is the following:

    I have userforms with which the user can select and deselect certain values/lines. For revision handling purposes these lines are either hidden or given strikethrough. If a sub-paragraph of options is deselected I also need to handle the sub-paragraph title. The titles themselves aren't ranges (maybe I should change this) and that's why I use the offset of -1 rows.

    So to repeat what I hope to achieve... rather than handling each row individually with a number of columns, deny Excel to change anything beyond, say, column 30. Thereby acting as if there only are 30 columns and meaning the entirerow.font.strikethrough doesn't add too much unnecessary formatting and making the file larger than necessary.

  9. #9
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,205

    Re: Limit entirerow columns with VBA code

    No.
    There is no way to change Excel so that the .EntireRow property only returns the first 20 columns.

    Find and replace might be used to replace .EntireRow with .EntireRow.Resize(,20) throughout a VB Project.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

+ 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. [SOLVED] Inserting Rows Between Two Columns (EntireRow doesn't work)
    By Building in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-04-2015, 04:37 PM
  2. [SOLVED] EntireRow.Insert with conditions vs a match VB code
    By Excelnoub in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-15-2013, 03:18 PM
  3. [SOLVED] Change/replace vb code .EntireRow.Insert (2007)
    By Excelnoub in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-25-2012, 07:46 AM
  4. Macro Error with code "Range("A65536").End(xlUp).EntireRow.Insert"
    By lukasj13 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-30-2010, 08:48 PM
  5. Replies: 4
    Last Post: 10-09-2005, 04:05 PM

Tags for this Thread

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