Variable Column Selections Macro

[Craig13_13]

Hello all

I'm trying to write a Macro for the following:
I have a list of suppliers in column A and each month a score is generated for that supplier and the data put in columns, i.e. B has january score, C has february score etc.

I want the macro to arrange the suppliers in order of the last entered score, for example if there is data up until the march column i want it to arrange them by that score.

So far I can get it to find the last column with data in, I can get it to highlight that one but the bit I'm struggling with is "dynamic selection" if you like, I can get it to highlight the final column, but not the final column plus all the previous ones.

All suppliers are given a score at once so there will never be a blank cell in a column as such - it's all or nothing!

Any help would be much appreciated.

 

[Boller]

Try generating your macro via the Macro Recorder - via Cells/Sort.

Once you've done that, you just need to "tweak" it to identify the last column - which you say you know how to identify.

 

[Craig13_13]

Thanks for the suggestion, unfortunately when you use the Macro recorder to select in this manner it sets a defined range (say "A6:D100"), and I can't figure out how to get that second number to be the end column

Highlighting end column of data code is as follows:

Range("a6").Select
Selection.End(xlToRight).Select
ActiveCell.EntireColumn.Select

 

[Weaver]

You should be able to replace you code with

Range("a6").End(xlToRight).EntireColumn.Select

but you don't need to 'select' a range in order to do things with it.

you could define the range of all the data in the last column as

lastCol = cells(6,columns.count).end(xltoleft)
set lastrange = range(cells(6,lastcol),cells(rows.count,lastcol).end(xlup))

 

[Boller]

Thanks for the suggestion, unfortunately when you use the Macro recorder to select in this manner it sets a defined range (say "A6:D100"), and I can't figure out how to get that second number to be the end column

Highlighting end column of data code is as follows:

Range("a6").Select
Selection.End(xlToRight).Select
ActiveCell.EntireColumn.Select

I really have no idea what you want to do, but merely to establish the column number from the code you have posted :-

Dim c%
Range("a6").Select
Selection.End(xlToRight).Select
c=Selection.Column