Hi guys,
I hope one of you gents can help me with the following issue.
I am trying to create a macro that would define names for each column in the attached sheet. I wanted the range's name to be the value in the first row and the range to be the last 90 values in the column. Right now I have three sets of data, but I would add more.Example00.xlsx
Thanks in advance!!
Highlight Range A1:XXXXX90, Go to Formulas, under defined names group, click on Create from selection, check Top row check box only.
Hi, thanks for your quick reply.
However, that is not exactly what I am looking for, perhaps I wasn't 100% clear - pls excuse my english.
I wanted a range for each column, refering to the last 90 entries, named after the first cell in the column. For example (in the example workbook), a range called Data1 for A166:1255, a range called ITSA4 for B166:B255, and so on.
Thanks again!
Oh... try . Some of the character is not useable for name range, so the macro skips the column that name cannot be used for name range
Sub Test() Dim LC As Long: LC = Worksheets("Base").Cells(1, Columns.Count).End(xlToLeft).Column Dim i As Long With Worksheets("Base") For i = 1 To LC On Error Resume Next .Range(Cells(.Cells(Rows.Count, i).End(xlUp).Row - 89, i), Cells(.Cells(Rows.Count, i).End(xlUp).Row, i)).Name = .Cells(1, i) Next i End With End Sub
Thanks a lot, JieJenn, it does work well.
To make it even better, is there a way to modify the formula so it would always get the last 90 entries, even after I add more? Something like offset.
Thanks again!
The macro will look at the column range regardless how many entries and then name range based on the last 90 items.
Yeah, I had not noticed it before. Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks