Can Cols.Width be forced to larger size below range and above smaller size or simlated

[gint32]

Hi everyone,

I am wondering if it’s possible to set column widths to a wider size than the above, using say a range, meaning if the range A1-A17 = column size 10.00 then have A18 –A100 = column size 20.00, I don’t wish to use merge cells or wordwap if possible.


Or if not at least simulate this with an over lay or what ever you can suggest as the best approach.


Reasons the top range(A1-A17 ) column width needs to be fixed and below I have dropdowns the contain values that are a lot larger than.

Any suggestion are welcome
Thanks for reading

 

[Rick Rothstein]

I am wondering if it’s possible to set column widths to a wider size than the above, using say a range, meaning if the range A1-A17 = column size 10.00 then have A18 –A100 = column size 20.00,

No, you cannot have the cells within a single column have different widths. Think about it... if you could, what would the next column over look like if it had a single width for all of its cells? I think the only way to achieve what you are describing is with merged cells (which would probably cause you problems later on).

 

[ISY]

You can change the width of the dropdowns
LinK http://cellmasters.net/cd_068.htm

Example

Place the following in a worksheet module.

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const ValidWidth = 2 'Change here to change the width of the Data Validation list
    If Target.Column = 1 Then MakeValidationWidthWide Target, ValidWidth
End Sub

Place the following in a standard module.

Option Explicit


Sub MakeValidationWidthWide(ByVal Target As Range, RelativeToOriginalSize)
    Dim wks As Worksheet
    Dim elmDic As Object
    Dim elmShp As Shape
    Dim drpShp As Shape
    Dim objDic As Object


    Set wks = Target.Parent
    On Error GoTo Terminate


    'When the AutoFilter is used in the worksheet
    'this procedure fails, so turn off the AutoFilter
    wks.AutoFilterMode = False


    If Target.Cells.Count > 1 Then Exit Sub


    If Target.Validation.Type = xlValidateList Then
        Set objDic = CreateObject("Scripting.Dictionary")
        For Each elmDic In wks.DrawingObjects
            objDic.Add elmDic.Name, elmDic.Name
        Next
        For Each elmShp In wks.Shapes
            If elmShp.Name Like "Drop Down *" Then
                If Not objDic.Exists(elmShp.Name) Then
                    Set drpShp = elmShp
                    Exit For
                End If
            End If
        Next
        If Not drpShp Is Nothing Then
            drpShp.ScaleWidth RelativeToOriginalSize, False, msoScaleFromBottomRight
            SendKeys "%{down}"
        End If
    End If
Terminate:
    Set drpShp = Nothing
    Set objDic = Nothing
End Sub

 

[gint32]

You can change the width of the dropdowns

[/CODE]

Thanks though not what I am after, need a way of simulating the OP, maybe with another sheet overlayed on top if possible.:

 

[gint32]

No, you cannot have the cells within a single column have different widths. Think about it... if you could, what would the next column over look like if it had a single width for all of its cells? I think the only way to achieve what you are describing is with merged cells (which would probably cause you problems later on).

Correct, and thanks now I know, Is it possible to overlay then to simulate and create the effect, or ??,,,thanks

 

[gint32]

No, you cannot have the cells within a single column have different widths.

Hi, Can I assume that this is not possible to replicate or do the gurus here in this forum need more info, if so here the reason why, the user(she) has a range that she wishes to remain fixed then below that will be a bout 12 drop down spread out across the sheet same worksheet, these cell sizes will vary depending on the individual its contents, so this is why she needs the top fixed and the below adjustable.
Hope this helps and explains the need for my original Post a Little better.
So, 3rd Time asking Can this be achieve visually without!!! Adjusting Columns and if so how to do? other than merging cells

 

[Fluff]

Simple answer is NO.
I would also advise against using merged cells as they will only come back to bite you on the RRs.