Automatically rearrange columns

[Sir Babydum GBE]

Ok,

Still not working properly here at work.

Getting the error "Unable to Set the Hidden property of the Range Class" on the line:
Columns(sColumns).Hidden = True.

I'm attaching the actual workbook now so that perhaps you can have a better Idea what's going on. The Starting columns are in different positions - but i think i sorted that in your code.

Really sorry about this, yet again, I'm beginning to feel like a little Yorkshire Terroriser!

(Also, some redundant lists were still being left in the custom lists thingy - I'm not sure that that was affecting things so I deleted the lists and started again - but i still got the error (and more redundant lists))

Oh, and what the blazes is the Wessex Liberation Front? Are you from Liberia or Wessex? Make up your mind!

[Bob Phillips]

Still not working properly here at work.

Getting the error "Unable to Set the Hidden property of the Range Class" on the line:
Columns(sColumns).Hidden = True.

What do you expect! You have a comment in cell CH1, and surely you have read this Microsoft KB article

http://support.microsoft.com?kbid=170081
XL97: "Cannot Shift Objects Off Sheet" Error Hiding Columns

Remove the comment, and it works fine.

Really sorry about this, yet again, I'm beginning to feel like a little Yorkshire Terroriser!

Terrorist! Sabotaging those good Welsh folks.

(Also, some redundant lists were still being left in the custom lists thingy - I'm not sure that that was affecting things so I deleted the lists and started again - but i still got the error (and more redundant lists))

That shouldn't be a problem, my last amendment caters for that, but it doesn't hurt to clear them if you see them. Once it is working fine, there should be no problem.

Oh, and what the blazes is the Wessex Liberation Front? Are you from Liberia or Wessex? Make up your mind!

That is quite a mind-leap to equate Liberation with Liberia. A hundred years ago, maybe, no today.

It is just an organisation of mad, sad old gits that would like to see the Kingdom of Wessex (remember Arthur?) devolved from the UK.

[Sir Babydum GBE]

Thanks TCP, you're a star.

So, what you're saying is "it was all your fault babydum, and being a Yorkshire boy probably doesn't help", is it?

It's all working now and I think I'll probably get the medal that you deserve!. I've put a thanks to you in the properties of the document (not allowed to put it anywhere else). I have another question, but that's about to appear in another thread because it's an entirely seperate question - though the answer will hopefully be able to be employed in the sheet you so masterfully fixed.

[Bob Phillips]

Thanks TCP, you're a star.

TCP - because I disinfect the wounds inflicted by others?

So, what you're saying is "it was all your fault babydum, and being a Yorkshire boy probably doesn't help", is it?

No, what I am saying is that anyone who doesn't know every KB article that pertains to Excel off my heart shouldn't be let anywhere near a spreadsheet.

It's all working now and I think I'll probably get the medal that you deserve!. I've put a thanks to you in the properties of the document (not allowed to put it anywhere else).

We long-suffering developrs are well used to that, rarely do we even get the hidden thanks. Seriously though, I enjoyed this one. I especially liked being able to use custom lists on the fly, and will keep that one in my armoury for future use.

I guess that is at least one thing they do well in Yorkshire.

[Sir Babydum GBE]

Ok, I have another last question

I'm sure that this will be trickier, but you have brains and I have a pick.

In the example workbook above, the code that you ("you" being xld - in case you're not xld. If you are xld - ignore what's in these parentheses) have created sorts the columns according to the "Role" on any given line, and hides the the columns that do not need to be seen.

Can a variation of the code - run as a one off macro - put "n/a" in all the cells that would be hidden for each delegate, and "pending" for all the cells that would be visible for each delegate?

World peace is at stake again.

Ta very much

[Bob Phillips]

Ok, I have another last question

I'm sure that this will be trickier, but you have brains and I have a pick.

In the example workbook above, the code that you ("you" being xld - in case you're not xld. If you are xld - ignore what's in these parentheses) have created sorts the columns according to the "Role" on any given line, and hides the the columns that do not need to be seen.

Can a variation of the code - run as a one off macro - put "n/a" in all the cells that would be hidden for each delegate, and "pending" for all the cells that would be visible for each delegate?

World peace is at stake again.

Ta very much

I thought your company had done vbax a favour and banned you from playing with Excel

Your workbook has been removed from your previous post for Data protection reasons. You could mail me a copy in a PM, or post a cleaned version to workl on.

[Sir Babydum GBE]

I thought your company had done vbax a favour and banned you from playing with Excel

Right, that's it! My oven is well and truly off. No cake for you if you visit, mr mfi! The rule isn't in yet, and after having a chat with some guys from I.S. it looks as though it's not going to be enforcable. The upshot being that it will be against policy, but if an unregistered spreadsheet is used, they won't interfere (probably), but they won't support or fix problems either. And, seeing as I have never once reported a spreadsheet fault to the coding guys - primarily because there is a four-week turnaraound on such fault reports - but rather i use great forums such as this one when I run into trouble, I think i will continue with the project.

Your workbook has been removed from your previous post for Data protection reasons. You could mail me a copy in a PM, or post a cleaned version to workl on.

Oh, that's right - it was me that removed it. here it is.

Thanks

[Bob Phillips]

Right, that's it! My oven is well and truly off. No cake for you if you visit, mr mfi!

What, no cake! This isn't a fee service you know.

The rule isn't in yet, and after having a chat with some guys from I.S. it looks as though it's not going to be enforcable. The upshot being that it will be against policy, but if an unregistered spreadsheet is used, they won't interfere (probably), but they won't support or fix problems either.

That is a more pragmatic policy. You can understand the IS guys problems, trying to sort problems on thousands of spreadsheets by thousands of different developers is a daunting task. You either enforce a rigid poiicy of central spreadsheet development, no spreadsheet development, or you get pragmatic.

Oh, that's right - it was me that removed it. here it is.

It is not quick, but you will only run iot rarely.

I don't get why you want this though, with the other code you will never see the 'n/a's

Sub SettoNA()
Dim iLastCol As Long
Dim ilastRow As Long
Dim iColumn As Long
Dim iRow As Long
Dim nCourses As Long
Dim i As Long, j As Long, k As Long
    Const colCourses As String = "1:1"
    Const colModules As Long = 25
    Const colCourse As String = "I"
Application.ScreenUpdating = False
With Worksheets("Delegates")
         iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
         ilastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
         For i = 2 To ilastRow
            iColumn = 0
            On Error Resume Next
                iColumn = Application.Match(.Cells(i, colCourse).Value, Worksheets("Courses").Rows(colCourses), 0)
            On Error GoTo 0
            If iColumn > 0 Then
                For j = colModules To iLastCol
                    iRow = 0
                    On Error Resume Next
                        iRow = Application.Match(.Cells(1, j).Value, Worksheets("Courses").Columns(iColumn), 0)
                    On Error GoTo 0
                    If iRow = 0 Then
                        .Cells(i, j).Value = "n/a"
                    End If
                Next j
            End If
        Next i
    End With
Application.ScreenUpdating = True
End Sub

[Sir Babydum GBE]

What, no cake! This isn't a free service you know.

What? Not free? I'll put the oven back on then...

That is a more pragmatic policy. You can understand the IS guys problems, trying to sort problems on thousands of spreadsheets by thousands of different developers is a daunting task. You either enforce a rigid poiicy of central spreadsheet development, no spreadsheet development, or you get pragmatic.

Good put, well point.

It is not quick, but you will only run it rarely.

This is precisely why I wanted it in a seperate module. But to do that potentially 10,000 time for all sites... it's worth doing if you only use it once!

I don't get why you want this though, with the other code you will never see the 'n/a's

Well, I wasn't sure whether n/a's would be such an issue - but i plan to do pivot reports on the whole sheet, and I thought "n/a's would be better than blanks in case we find another use for the blanks". Just trying to think ahead - which if I managed to do a bit better, there wouldn't be all these last last questions.

So thanks for that xyz, muchos gracias amigo.