Excel

From ◜◝◟◞◜◝◟◞◜◝◟◞◜◝◟◞◜◝◟◞◜◝◟◞◜◝◟◞
Jump to: navigation, search

VBA

http://www.few.vu.nl/~rbekker/VBA-intro.pdf

Templates

VBA script to template

Power Query

Using Parameters inside Power Query

Concatenation, Delimit and String Output

Sub ChangeRange()
'Updateby20140310
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "ProceduresPatientRegistration"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
outStr = ""
For Each rng In InputRng
    If outStr = "" Then
        outStr = rng.Value
    Else
        outStr = outStr & "+" & rng.Value
    End If
Next
OutRng.Value = outStr
End Sub

[1]


Function csvRange(myRange As Range)
    Dim csvRangeOutput
    Dim entry as variant
    For Each entry In myRange
        If Not IsEmpty(entry.Value) Then
            csvRangeOutput = csvRangeOutput & entry.Value & ","
        End If
    Next
    csvRange = Left(csvRangeOutput, Len(csvRangeOutput) - 1)
End Function

[2]


Sub generatecsv() 

Dim i As Integer
Dim s As String

i = 1

Do Until Cells(i, 1).Value = ""
    If (s = "") Then
        s = Cells(i, 1).Value
    Else
        s = s & "," & Cells(i, 1).Value
    End If
    i = i + 1 
Loop

Cells(1, 2).Value = s

End Sub

[3]

Conditional Formatting

  1. Home ribbon > Styles group > Conditional Formatting > New Rule
  2. Under 'Select a Rule Type' choose: Use a formula to determine which cells to format
  3. Under 'Edit the Rule Description' type =MOD(ROW(),2)=1 in the 'Format values where this formula is true'
  4. Click the Format button and select your choices > OK

Column Widths

Exampleː

Sub SetColumnWidth() 
    Columns("A:B").ColumnWidth = 3.14 
    Columns("C").ColumnWidth = 8 
    Columns("D").ColumnWidth = 13.57 
    Columns("E").ColumnWidth = 24.14 
    Columns("F").ColumnWidth = 9 
    Columns("G").ColumnWidth = 10 
    Columns("H").ColumnWidth = 11.29 
    Columns("I").ColumnWidth = 8.57 
    Columns("J").ColumnWidth = 6.86 
    Columns("K").ColumnWidth = 8 
    Columns("L").ColumnWidth = 13 
    Columns("M").ColumnWidth = 10 
End Sub

[4] https://msdn.microsoft.com/en-us/library/office/ff837430.aspx

http://analysistabs.com/excel-vba/change-row-height-column-width/

References

  1. https://www.extendoffice.com/documents/excel/1544-excel-convert-column-to-comma-separated-list.html
  2. https://superuser.com/questions/240858/convert-a-column-into-a-comma-separated-list
  3. https://superuser.com/questions/240858/convert-a-column-into-a-comma-separated-list
  4. http://www.ozgrid.com/forum/showthread.php?t=84796