When data is added, there is a table that is added to the right column, and I want to get the column number of the rightmost column with data I created the following code.

co = Cells (g, 1) .End (xlToRight) .Column

There is a blank space between the rightmost columns, so I tried to get the row number by counting from the right side, but it doesn't work.
(Actually, it is column I (9), but it is displayed as 2.)

It may be a rudimentary question, but I didn't understand why the different numbers were displayed.
We would appreciate it if you could give us advice on the cause and improvement method.

  • Answer # 1

    The method I'm doing now is the first column of the specified rowCells (g, 1)To the right starting fromxlToRightWe are doing the exact opposite by looking for the final point towards.
    Last row in the sheetColumns.CountTo the leftxlToLeftIf you look for the final point toward, it will be as follows.

    co = Cells (g, Columns.Count) .End (xlToLeft) .Column

  • Answer # 2

    If the environment assumes that there are no blank lines,
    You can get the last row just by applying an appropriate filter.

    If you apply a filter, it will filter to the last column including blanks unless there are blank rows.
    From the question text, it seems that there is no empty string in the middle.

    So, if you look at "Sheet.AutoFilter.Range.Address"
    "$A $1: $F $14" You can get an address like this.

    If you specify xlR1C1 for ReferenceStyle of Address
    "R1C1: R14C6" You can get it like this.

    After that, you can find out column F or column number 6 by character string search.
    So, if you don't use it, remove the filter and you're done.

  • Answer # 3

    co = Cells (g, Columns.Count) .End (xlToLeft) .Column
    How about it?

  • Answer # 4

    I'm sorry. I'm not sure what I want to do.
    In the attached figure
    When G = 2, co = 7
    When G = 3, co = 4
    When G = 4, co = 5
    Is it different from wanting to get?
    If so, would you be able to get a good answer by presenting and showing the actual diagram?