STICKY for Code Snippets

[geecee]

Might I make a suggestion for a ‘sticky’ topic - Code Snippets – where Members can post small pieces of code which may be helpful to others, and where they can look before posting a query on ‘how to’.

I realise there is already a ‘sticky’ topic – Useful Excel and VBA References – under Excel in the Office Applications section but whilst it is indeed most helpful, it references Web sites to look up from which the Member may choose one which doesn’t relate to the query they have in mind.

It would, of course, require strict policing and might mean some posts get rejected but I believe it could be useful. I have listed below two examples of what might be considered.

This code shows how to change the column width in multiple sheets simultaneously.

Code: Select all

Sub Change_Column_Width()

  Dim worksheetnames() As Variant
  worksheetnames = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5") 'and other worksheets
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  '   worksheetnames = Array("Operations", "Staff", "Support") 'and other worksheets  '
  '   CHANGE worksheet NAMES TO SUIT                                                  '
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Application.ScreenUpdating = False
    For i = 0 To UBound(worksheetnames)
        MsgBox worksheetnames(i)
        With Worksheets(worksheetnames(i))
            .Columns("A:A").ColumnWidth = 11.5
            .Columns("B:B").ColumnWidth = 8.75
            .Columns("C:G").ColumnWidth = 11.5 ' <<<<<<<<< Adjacent columns
            .Columns("H:H").ColumnWidth = 2.75
            .Columns("I:I").ColumnWidth = 11.5
            .Columns("J:J").ColumnWidth = 11.75
            .Columns("R:R").ColumnWidth = 13.38
            '''''''''''''''''''''
            ' and other columns '
            '''''''''''''''''''''
        End With
    Next i
    Application.ScreenUpdating = True
  
End Sub

This code shows how to apply text to merged cells.

Code: Select all

Sub Mergethecells()

    Range("I19:K20").Select ' < Change to suit needs

    Range("I19").MergeArea.Value = "SELECT AND COPY ABOVE AND PASTE TO THE HEADING"  ' < Change to suit needs

    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    
End Sub 

[AlanMiller]

Sounds like a good idea. I have a couple I could contribute. It would be important that the Subject description is carefully worded to give a clear indication of the utility of the code.

Alan

[HansV]

Hi,

We have discussed this among the admins. We appreciate the suggestion, but we fear that such a thread would quickly become unmanageable. It would constantly have to be reviewed, indexed, reordered etc.
Since search engines such as Google index our content automatically, it is more convenient to search for specific subjects using your favorite search engine.

For example, to search for the phrase merged cells in Eileen's Lounge, use the following search criteria:

"merged cells" site:eileenslounge.com

[geecee]

Hi,

We have discussed this among the admins. We appreciate the suggestion, but we fear that such a thread would quickly become unmanageable. It would constantly have to be reviewed, indexed, reordered etc.

Thanks for your reply Hans. I kind of thought that might happen. Ah well, no harm in asking.

[geecee]

Sorry to bring this up again but I do believe something along the lines of Code Snippets would be worthwhile. What might the problem be if say I posted something under the heading “Counting words in text” or “Splitting text at the Nth word”. They would not of course be asking the question but rather supplying a solution. I note that Alan also thinks it might be worthwhile. In regard to both these subjects, I spent ages “google-ing”, or in my case “duckduckgo-ing”, before I found what I was looking for whereas, to use Hans’ suggestion, I could just have looked up “Counting words in text” site:eileenslounge.com or “Splitting text at the Nth word” site:eileenslounge.com had they of course been there.

[Rudi]

Hi geecee,

Could we suggest that you start a poll (in the Lounge Matters forum) that prompts respondents for a yes/no (or something similar) on your suggestion. Let's get an idea of the interest, and with a favorable response to the poll, we would go ahead with your request.