Results 1 to 8 of 8

Count columns with data in them

This is a discussion on Count columns with data in them within the Excel Questions forums, part of the Question Forums category; I am trying to count the amount of columns with actual data in them. For instance I have columns from ...

  1. #1
    Board Regular
    Join Date
    May 2002
    Location
    Auckland
    Posts
    413

    Default

    I am trying to count the amount of columns with actual data in them. For instance I have columns from A - J in one sheet but A to K in another, the code I will run on them will need to know when to stop.

  2. #2

    Join Date
    Nov 2002
    Posts
    981

    Default

    lastCol = Range([A1], ActiveSheet.UsedRange).Columns.Count

  3. #3
    Board Regular
    Join Date
    May 2002
    Location
    Auckland
    Posts
    413

    Default

    Not sure what to make of this one.
    When I use the code mentioned I get 41 when there are only 10 cloumns with data in them, so 31 are superfluous. Cell/column 41 is empty as well.

  4. #4
    Board Regular btadams's Avatar
    Join Date
    Jan 2003
    Location
    Richmond, Va
    Posts
    1,871

    Default

    The simple solution would be:

    intColumns = ActiveSheet.Usedrange.Columns.Count

    however if there are empty columns inbetween A and J, this will count them as well. Also this will cause problems if some cells outside the "real" usedrange of the worksheet have some special formatting, such as a date format, then Excel will see that cell as being part of the usedrange even tho it doesn't contain anything

    there are other ways if necessary

  5. #5
    Board Regular
    Join Date
    May 2002
    Location
    Auckland
    Posts
    413

    Default

    For some really strange reason this is coming up with 41 as well??????
    I have made sure that there is no data from J on, there are also no blank columns/rows between A and J on my test sheet.

  6. #6
    Board Regular btadams's Avatar
    Join Date
    Jan 2003
    Location
    Richmond, Va
    Posts
    1,871

    Default

    could there be empty columns between A & J? If so will it cause a problem if the macro operates on these columns? If so we can do a check to see if the column is empty by using

    If Application.CountA(Columns(i)) <> 0 Then
    'put your code here
    else
    'move to next column
    endif

  7. #7

    Join Date
    Nov 2002
    Posts
    981

    Default

    lastCol = Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByColumns).Column

  8. #8
    Board Regular
    Join Date
    May 2002
    Location
    Auckland
    Posts
    413

    Default

    Ponsonby gets first prize,
    LastCol = Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column
    works perfectly.

    Thanks everyone!!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com