Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: count the number of columns until the first blank cell is encountered.

  1. #1
    Join Date
    17th June 2013
    Posts
    157

    count the number of columns until the first blank cell is encountered.

    hello all,

    i am trying to count the number of columns in my worksheet, the below code does that but i want the count function to stop when it encounters a blank column value... what changes should i make

    VB:
    lastCol = Worksheets("Dashboard").Cells(3, Columns.Count).End(xlToLeft).Column 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    2,939

    Re: count the number of columns until the first blank cell is encountered.

    Hi Mathew

    If you mean you want the next column along just add one to the statement.

    VB:
    lastCol = Worksheets("Dashboard").Cells(3, Columns.Count).End(xlToLeft).Column +1 
    
    
    Take care

    Smallman

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    17th June 2013
    Posts
    157

    Re: count the number of columns until the first blank cell is encountered.

    hello smallman,

    no that is not what i meant, basically from row 3 in column a i have around 47 columns, now the code which i have posted returns the number.
    what i wish to achieve is a count of the column till a blank cell is encountered (the count now till blank cell is 14. i do want to set the number as 14 as the count keeps changing), since everything after that is irrelevant to me.

    warm regards,

    Mathew

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    2,939

    Re: count the number of columns until the first blank cell is encountered.

    Hi Mathew

    I can only make assumptions on your description. What a file does is take ambiguity away from any and every post. I can only assume this is what you mean but a file removes the chance for an error.
    Post a file if the below misses the mark

    VB:
    lastCol = Cells(3, 1).End(xlToRight).Column + 1 
    
    
    The above assumes your data commences in A3.

    Smallman

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    17th June 2013
    Posts
    157

    Re: count the number of columns until the first blank cell is encountered.

    hello Smallman,

    file.jpg
    yea i get that , sorry for being so vague
    as you see in the image posted i want the count function to count from a3 to o3,but there are still files after p3 i do not want the count for them.
    the code i have posted does that.
    does this help.

    Warm Regards,
    Mathew

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    2,939

    Re: count the number of columns until the first blank cell is encountered.

    Did you try the code I posted above. It will produce a result that will give you the first blank column starting in A3 no matter the length.
    See file for example of variable col length capture.


    Smallman
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    17th June 2013
    Posts
    157

    Re: count the number of columns until the first blank cell is encountered.

    Hello Smallman,

    it worked thank you

    Warm Regards,
    Mathew

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    2,939

    Re: count the number of columns until the first blank cell is encountered.

    No worries Mathew

    Take it easy

    Smallman

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 10
    Last Post: March 21st, 2013, 04:45
  2. count number of cells since last blank cell
    By ajb12 in forum EXCEL HELP
    Replies: 3
    Last Post: December 30th, 2012, 04:13
  3. Count number of cells/rows until first blank cell
    By msuspartan11 in forum EXCEL HELP
    Replies: 2
    Last Post: June 23rd, 2010, 20:44
  4. Replies: 1
    Last Post: June 29th, 2006, 17:06
  5. Count Based On Criteria and Reset If Blank Encountered
    By Maxemily in forum Excel and/or Word Help
    Replies: 6
    Last Post: October 9th, 2004, 12:05

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
  •  
porno