Results 1 to 4 of 4

Thread: xldown and redefine the column name macro

  1. #1

    Exclamation xldown and redefine the column name macro

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Good day Guys , I am new to here and seeking great help from you guys, I need a macro that , when i toggle the macro, it will go to every column xldown and redefine the column name using the top name like alpha beta and carrot, if i input another info in the column it will auto redefine it to group it to the same column name....

    alpha | Beta | Carrot
    asd |asd | asd
    asd |asd | asd
    asd |asd |
    asd | |

    i want to be when i input new data to the existing column, it will select until the xldown and redefine the column name by using the top name....

    Hope to get the answer from you guyz soon...

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    Dim Lastrow As Long, Lastcol  As Long
    Dim i As Long
        With ActiveSheet
            Lastcol = .Range("A1").End(xlToRight).Column
            For i = 1 To Lastcol
                Lastrow = .Cells(1, i).End(xlDown).Row
                .Names.Add Name:=.Cells(1, i).Value2, RefersTo:="=OFFSET(" & .Cells(1, i).Address & ",0,0,COUNTA(" & .Columns(i).Address & "),1)"
            Next i
        End With

  3. #3
    I am getting run time error "1004"

    the situation now is my data all in sheet 2

    i use =INDIRECT(CONCATENATE(B21,"_M")) to link my sheet 2 data , i needed a code that will able to toggle the macro that when i input new data to the sheet 2 ,it will automatically redefine the whole column until x1down to the name at the top column

    i am still new in this , sorry for any inconvenience and thanks for reply

  4. #4
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Manchester, England
    Excel Version
    Excel 2016
    Why not attach a sample workbook, this way we can actually see your data type and structure and tailor something toward that
    Kind regards,
    Simon Lloyd

Posting Permissions

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