+ Reply to Thread
Results 1 to 5 of 5

How to create a dynamic range if the last cell is empty and the columns have empty cells

  1. #1
    Registered User
    Join Date
    05-14-2012
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    94

    How to create a dynamic range if the last cell is empty and the columns have empty cells

    Hi again!
    I want to create a dynamic range in column K. The static range is Range("K2:K821")
    The dynamic range I then use for counA and CountIF
    Since the last row in column A always is nonblank, i thought using offset
    Please Login or Register  to view this content.
    the last row is correctly set to 281, but when I put it in the range, then select row 2821 and only 1 blank cell
    please help me out
    Thanks in advance
    Best regards
    //Peter

  2. #2
    Registered User
    Join Date
    05-14-2012
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: How to create a dynamic range if the last cell is empty and the columns have empty cel

    It seems like the range
    Please Login or Register  to view this content.
    is wrongly written because when i rewrite the code
    Please Login or Register  to view this content.
    it works like intended
    How should I construct the range code correctly?
    Another question: why do not countA
    Please Login or Register  to view this content.
    give the right answer, when the right range is selected? the answer I get is 32 when it should be 30. which is exaktly what this code
    Please Login or Register  to view this content.
    gives
    Last edited by peter_swe; 08-03-2016 at 07:41 PM.

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

    Re: How to create a dynamic range if the last cell is empty and the columns have empty cel

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    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
    05-14-2012
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: How to create a dynamic range if the last cell is empty and the columns have empty cel

    Thank you TMS you saved my brain
    Best Regards
    //Peter

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: How to create a dynamic range if the last cell is empty and the columns have empty cel

    You're welcome. Thanks for the rep.

+ 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. Replies: 0
    Last Post: 08-03-2016, 07:01 PM
  2. Replies: 2
    Last Post: 07-21-2016, 09:40 PM
  3. [SOLVED] Colour fill of empty cells in a dynamic range
    By Coems in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-19-2014, 06:27 PM
  4. MACRO to empty a range of cells based on whether specified cell is empty
    By TBJV in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2013, 10:35 PM
  5. Select dynamic range based on non-empty cells
    By oOarthurOo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2009, 01:42 PM
  6. check if any cell in a dynamic range is empty
    By akabraha in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-30-2008, 04:13 PM
  7. Finding next empty empty cell in a range of columns
    By UncleBun in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2006, 07:25 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