Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

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

  • #2
    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.

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

    Smallman
    sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

    Comment


    • #3
      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

      Comment


      • #4
        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

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

        Smallman
        sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

        Comment


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

          hello Smallman,

          Click image for larger version

Name:	file.jpg
Views:	1
Size:	14.9 KB
ID:	1113375
          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

          Comment


          • #6
            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
            sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

            Comment


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

              Hello Smallman,

              it worked thank you

              Warm Regards,
              Mathew

              Comment


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

                No worries Mathew

                Take it easy

                Smallman
                sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

                Comment

                Working...
                X